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

# 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
• 3
• 3
• 2
• +1
1 Solution

Commented:
<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

Commented:
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

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

Author 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

Commented:
SMALLINI ... do you mean SMALLINT?  I have never heard of SMALLINI
0

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

Author 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

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>
``````

... 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>
``````
0

Author 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

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