Is it possible to get recordcount in nested cfoutputs when using query and group attributes? ie

<cfquery name="test">select firstname from names order by firstname asc</cfquery>

<cfoutput query="test" group="firstname">

<cfoutput>

#test.recordcount# <!--- total number of people with this first name --->

</cfoutput>

</cfoutput>

test is a query object. recordcount is a single number representing the the total number of records returned from that query object. So when you output test.recordcount, you will return the total number of records returned from the query. The location inside of a group or otherwise is irrelevant to the outcome. To return the total number of records with a particular value, you would likely need a count subquery in your SQL statement. The syntax of that subquery would vary based on the DB you're querying.

trying to simplify my example I realized I made a mistake. #test.recordcount# is outside of the nested cfoutput.

I had to do something along those lines in an app I was building for a financial aid group at a university a while back. Doing it in plSQL made me want to drink paint lol, but it totally works. Just gets busy in there.

You don't need to mess around with your data fetch. You have all the info you need already. Just... don't combine data processing with data display: https://trycf.com/gist/ac93d056a1a56b3049023c9fde0819b3/acf2021?theme=monokai

<cfscript>
people = queryNew("id,firstName,lastName", "integer,varchar,varchar", [
    [1, "Anna", "Apple"],
    [2, "Anna", "Banana"],
    [3, "Brian", "Cherry"],
    [4, "Claire", "Date"],
    [5, "Claire", "Eggplant"],
    [6, "Claire", "Fig"],
    [7, "Dave", "Grape"]
])

namePopularity = people.reduce((names, person) => {
    return names.insert(
        person.firstName,
        names.keyExists(person.firstName) ? ++names[person.firstName] : 1,
        true
    )
}, {})

</cfscript>

<cfoutput query="people" group="firstname">
    #firstName#: #namePopularity[firstName]#<br>
    <ul><cfoutput><li>#lastName#</li></cfoutput></ul>
</cfoutput>

Result:

Anna: 2
* Apple
* Banana
Brian: 1
* Cherry
Claire: 3
* Date
* Eggplant
* Fig
Dave: 1
* Grape

Thanks Adam, I’ll have to unpack that a little bit but I appreciate the response. Would you approach this the same way regardless of the size of

people

?

If the data set was big, I might do a

SELECT firstName, COUNT(1) FROM people GROUP BY firstName

as well as the query to get the actual data. There will be a point at which the overhead of the second DB hit would become less than the overhead of doing the

reduce

on a big recordset, yeah. I'd try both 😉

You could also just count the rows with an inner cfoutput loop like:

<cfquery name="test">select firstname from names order by firstname asc</cfquery>
<cfoutput query="test" group="firstname">
<cfset thisnamecount=0>
  <cfoutput>
      <cfset thisnamecount++>
  </cfoutput>
#test.firstname# #thisnamecount#
</cfoutput>

Just as yet-another-way - if you wanted to do it at the database level then windows functions are really handy for things like this:

Ooooooh I didn't know about that site Alias! Thank you 🙂