Find total count in sql table group

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))))#
</cfoutput>

Open in new window

JanrowAsked:
Who is Participating?
 
azadisaryevConnect With a Mentor Commented:
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)#)
</cfquery>

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

Azadi
0
 
azadisaryevCommented:
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...
0
 
JanrowAuthor Commented:
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?
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
JanrowAuthor Commented:
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?
#Ceiling(ArraySum(ListToArray(ValueList(file_count))))#

Open in new window

0
 
azadisaryevCommented:
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...
0
 
JanrowAuthor Commented:
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">
 SELECT *
 FROM ORDERS   
 WHERE CID = '#CID#'
</CFQUERY>
 
 
<!--- Catalog_id is the same as AREA_ID here --->
<!--- FILE_COUNT is stored here ---> 
<cfquery name="AreaInfo" datasource="CDLIB">
 SELECT *
 FROM AREAS
 WHERE AREA_ID = #OrderInfo_CATALOG_ID#
</cfquery>
 

Open in new window

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

WHERE AREA_ID = #CATALOG_ID#
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.
0
All Courses

From novice to tech pro — start learning today.