Find total count in sql table group

Posted on 2009-02-15
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

Question by:Janrow
    LVL 27

    Expert Comment

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

    Author Comment

    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?

    Author Comment

    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

    LVL 27

    Expert Comment

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

    Author Comment

    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 *
     WHERE CID = '#CID#'
    <!--- Catalog_id is the same as AREA_ID here --->
    <!--- FILE_COUNT is stored here ---> 
    <cfquery name="AreaInfo" datasource="CDLIB">
     SELECT *

    Open in new window

    LVL 27

    Accepted Solution

    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


    Author Closing Comment

    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.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
    A web service ( is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now