• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Find grant total for multiple sql smallini columns

<cfquery name="LocalNews" datasource="NEWS1">
       select *
       from NewsAreas
       where area_id = 6
</cfquery >

I want to find the grand total numerical amount when adding multiple SMALLINI data type columns. Show me how using my query example with cf8 and sql 2000.
0
Qsorb
Asked:
Qsorb
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Jamieo1Commented:
<cfquery name="LocalNews" datasource="NEWS1">
       select SUM(SmallIni1) as grand1,SUM(SmallIni2) as grand2 ,SUM(SmallIni3) as grand3
       from NewsAreas
       where area_id = 6
</cfquery >
0
 
lwadwellCommented:
In SQL you use the SUM() aggregation function to total numeric amounts, e.g.

SELECT sum(some_column)
FROM some_table

If you want the totals (subtotals) per another column

SELECT group_column, sum(some_column)
FROM some_table
GROUP BY group_column
0
 
_agx_Commented:
One important thing to remember is SMALLINT has a max of 32,767. So when using SUM, the total may overflow that SMALLINT data type. If that's possible, you need to cast the columns to a data type with a greater capacity first, such as INT (max is 2,147,483,647)

          SELECT   SUM(   CAST(columnA as int)
                               +  CAST(columnB as int)
                        ) AS GrandTotal
          FROM      NewsAreas
          WHERE    area_id = 6

Also, if the base columns allow nulls, use ISNULLto convert null amounts to 0.

         SELECT   SUM(   CAST(ISNULL(columnA,0) as int)
                       +  CAST(ISNULL(columnB,0) as int)
                   ) AS GrandTotal
          FROM      NewsAreas
          WHERE    area_id = 6

I'm beat, so off to sleep for me. Later!
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
QsorbAuthor Commented:
My numbers are far below 32k so that's not a problem to continue to use smallini.

There's only one ini column so I'm a bit confused how to construct this:

Column FILE_COUNT is the SMALLINI.


<cfquery name="LocalNews" datasource="NEWS1">
   SELECT SUM(CAST(FILE_COUNT as int))
   AS GrandTotal
   FROM NewsAreas
   WHERE area_id = 6
</cfquery>  


#GrandTotal#


This isn't working. Perhaps my removing the second SMALLINI field screwed it up? What am I missing?
0
 
lwadwellCommented:
SMALLINI ... do you mean SMALLINT?  I have never heard of SMALLINI
0
 
_agx_Commented:
Edit: When you say "not working", what's the actual result? If it's a SMALLINT, then the output of the sum()  ie

        <cfoutput>#LocalNews.GrandTotal#</cfoutput>

should either give you a number -OR- possibly NULL if there are no matching area_id = 6 records, or if all values in the column are null. You can use val() to force a 0 when the result is null:

          <cfoutput>GrandTotal: #val(LocalNews.GrandTotal)#</cfoutput>
0
 
QsorbAuthor Commented:
Been sick so just returned here.

Let me restate this so I'll hopefully be less confusing.

When I use this code exactly, it returns a list of the total number of files (file_count) for each area but it does not add the list together. As an example, using this code:

<CFQUERY NAME="Keywords" DATASOURCE="NEWS1">
   select *
   from category_keywords
   where ','+ catword +',' LIKE '%,massage,%'    
 </CFQUERY>

<cfoutput query="Keywords">

 <cfquery name="AreasInfo" datasource="NEWS1">
   SELECT SUM(CAST(FILE_COUNT as int))
   AS GrandTotal
   FROM NewsAreas
   WHERE area_id = '#Keywords.area_id#'
</cfquery>  

#AreasInfo.GrandTotal#

</cfoutput>

#AreasInfo.GrandTotal# then expands to:  19 14 22 3 11 9 48 6 11 28 29

Yes, those numbers are correct but what I've been attempting to do is to add those numbers, that is, I want the sum of those numbers.  That's all I need.
0
 
_agx_Commented:
Ok, instead of running query w/in a loop, create a list of the area id's from the 1st query. Then feed it into the 2nd query. That'll give you a single total of 200 (ie 19+14+22+3+11+9+48+6+11+28+29)

<CFQUERY NAME="Keywords" DATASOURCE="NEWS1">
   select *
   from category_keywords
   where ','+ catword +',' LIKE '%,massage,%'    
 </CFQUERY>

<!--- convert all id's into a single list --->
<cfset areaIDList = valueList(Keywords.area_id)>
<cfquery name="AreasInfo" datasource="NEWS1">
   SELECT SUM(CAST(FILE_COUNT as int)) AS GrandTotal
   FROM    NewsAreas
   WHERE area_id IN (
             <cfqueryparam value="#valueList(Keywords.area_id)#" 
                                    cfsqltype="cf_sql_integer" 
                                   list="true"> 
              )
</cfquery>  
<cfoutput>
     Grand total = #AreasInfo.GrandTotal#
</cfoutput>

Open in new window


... OR just use a single query to get the totals

ie
<cfquery name="AreasInfo" datasource="NEWS1">
SELECT SUM(CAST(na.FILE_COUNT as int)) AS GrandTotal
FROM   NewsAreas na
WHERE  EXISTS (
       SELECT key.area_id
       FROM   category_keywords key
       WHERE  ','+ key.catword +',' LIKE '%,massage,%'    
       AND    key.area_id = na.area_id
)
</cfquery>

<cfoutput>
     Grand total = #AreasInfo.GrandTotal#
</cfoutput>

Open in new window

0
 
QsorbAuthor Commented:
That's what I wanted. I used your "SELECT SUM(CAST(na.FILE_COUNT as int)) AS GrandTotal" suggestion. Looks like in my groping I had no idea to use the CAST function, parameter. Works great. Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now