count total records for each user

Question

I have this code that pulls all users with open requests. I need to show a total for each assigned buyer.

<CFQUERY  NAME="allbuyers" DATASOURCE="procuresource3">
    SELECT *
    FROM allorders3
    WHERE open = 'y'  
    order by assignedbuyer
</cfquery>

<cfoutput query="allbuyers">  
    #assignedbuyer# #prn#
</cfoutput>

<cfoutput>
    Total: #allbuyers.RecordCount# 
</cfoutput>

The output is all records. I need a total for each user. Something like this below:

Christine - 301366
Christine - 300729
Christine - 300731
Christine - 300732
Total: 4
<br>
Connie    - 301524
Connie    - 301516
Connie    - 301510
Connie    - 301509
Connie    - 301460
Connie    - 301362
Total: 6
<br>
Dannette  - 301478
Dannette  - 301458
Dannette  - 301340
Total: 3

Thank you in advance. CarlosL

You could try iterating. `` will add to `theCount` every time you loop over a row, and you could output the final result at the end. This is assuming you are using a cfoutput and grouping by the "assigned buyer". — TRose, Oct 08 '19 at 19:16
What dbms? It might be easier to calculate a total from the db itself. — Shawn, Oct 08 '19 at 19:30

Dan Roberts · Answer 1 · 2019-10-08T22:09:09.783

There are multiple ways to accomplish this with query adjustments to get counts and building up struct counts by unique key.

The most straight forward with where you are currently is to build up a count based on whether the current assignedbuyer is the same as the previous assignedbuyer. CF provides a built-in way to have code run in a loop when a query group column changes.

<cfset q = queryNew("") />
<cfset queryAddColumn(q, "name", "varchar", ['Dan','Dan', 'Bob', 'Bob', 'Bob', 'Chris']) />

<cfoutput query="q" group="name">
    <cfset count = 0 />
    <p>
        <cfoutput>
            #name#<br />
            <cfset count += 1 />
        </cfoutput>
        Total: #count#
    </p>
</cfoutput>

Output

<p>
    Dan<br>
    Dan<br>
    Total: 2
</p>
<p>
    Bob<br>
    Bob<br>
    Bob<br>
    Total: 3
</p>
<p>
    Chris<br>
    Total: 1
</p>

I like this approach. I would use `` just because I like typing less — James A Mohler, Oct 09 '19 at 14:46

count total records for each user

1 Answers1

Linked