We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Find total count in sql table group

Janrow asked
Medium Priority
Last Modified: 2013-12-24
I'd like to find the sum of records in all catalog_id's. Each catalog_id has 25 to 50 or so records, but my code does not give me the total for all of them, just the total for each.

How should I modify my code to get the sum of all records?
<cfquery nane="OrderInfo" datasource="cdlib">
 select *
 from areas
 where area_id =  #catalog_id#
</cfquery >
<cfoutput query="OrderInfo">
 Total records = #Ceiling(ArraySum(ListToArray(ValueList(file_count))))#

Open in new window

Watch Question

you must specify query name and column name in ValueList function: ValueList(queryname.columnname)

but you do not need to use that functions at all. just do:
<cfoutput>Toatl Records: #arraysum(OrderInfo['file_count'])#</cfoutput>

assumes you want the sum of values of FILE_COUNT column of your query...


Get this error when adding that line, either inside or outside the cfoutput query:

[Table (rows 12 columns CATALOG_ID, CID, COST): [CATALOG_ID: coldfusion.sql.QueryColumn@1daa62d] [CID: coldfusion.sql.QueryColumn@1d3e8cc] [COST: coldfusion.sql.QueryColumn@158ab0a] ] is not indexable by file_count

What now?


Sorry, no error. A typo on my part.

But the result is the same as using my original code below.

When I place your code ( <cfoutput>Toatl Records: #arraysum(OrderInfo['file_count'])#</cfoutput> ) after the cfoutput query, I get one record.  I get only the total for the last catalog_id record. There is no sume for all catalog_id's.

When I place your code inside the cfoutput query, I get a list of all catalog_id totals, but not the grand total. So, unless I'm confused, which is so possible, we're back to where I was before.

What next?

Open in new window

i am confused... looking at your OrderInfo query - it returns data for one specific clatalog_id...

where are the other catalog_ids you are talking about coming from???
is your OrderInfo query inside some loop???

please post all relevant code - the code you posted shows that you have one query that returns data for one specific catalog_id...


Well, I apologize for pointing you in the wrong direction. You've just shown how confused I am.
But your questions got me thinking. About time. Wish I had before I asked the question.
Looks like I transposed some names earlier. Sorry.

So, maybe this explanation will help. It's late so hope I make some sense. If not, just ask.

Each Area_ID (catalog_ID) contains a few dozen files names. Each type or brand of file is stored as an area (AREAS) in the CDLIB db and saved as catalog_id's in the CART DB. They're the same. Catalog_ID sounds better for the customer.

I want to know the total file count the customer has ordered, how many CD's. The variable CID is the customer order number and references that unique customer. Catalog_ID's are also unique.

<!--- Contains customer orders organized by catalog_id. Each catalog_id has it's own row, no comadelimited records. --->
<CFQUERY NAME="OrderInfo" datasource="CART">
<!--- Catalog_id is the same as AREA_ID here --->
<!--- FILE_COUNT is stored here ---> 
<cfquery name="AreaInfo" datasource="CDLIB">

Open in new window

if i understand your explanation correctly, your OrderInfo query will return multiple CATALOG_IDs?

the way you have your second query (AreaInfo) right now - NOT inside a cfoutput/cfloop of your OrderInfo query, and NOT with a WHERE ... IN (...) clause - it will return records ONLY for the FIRST catalog_id from OrderInfo query.

you need to either:
a) have your AreaInfo query inside a cfoutput of OrderInfo query, and keep a runnning total of file_count - not very efficient...
b) or, better, use a WHERE column IN (list_of_values) construct:

<cfquery name="AreaInfo" ....>
SELECT file_count
FROM areas
WHERE area_id IN (#valuelist(OrderInfo.catalog_id)#)

now #arraysum(AreaInfo['file_count'])# should give you the sum of all FILE_COUNT values for all CATALOG_ID returned by your OrderInfo query


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Experts was offline for much of today so this was my first chance to get back.

AreaInfo was and is inside the cfoutput query. Thought that was but guess there's too much asuming.

Anyway in my AreaInfo query I substutited my existing:

for your:
WHERE area_id IN (#valuelist(OrderInfo.catalog_id)#)

Works great. I see valuelist is the key here which I'll try to remember.

Thanks for the great help.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.