Solved

# Find grant total for multiple sql smallini columns

Posted on 2012-09-16
398 Views
<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
Question by:Qsorb

Expert Comment

<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

LVL 25

Expert Comment

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

LVL 51

Expert Comment

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 Comment

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

LVL 25

Expert Comment

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

LVL 51

Expert Comment

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 Comment

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

LVL 51

Accepted Solution

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 Closing Comment

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function