Get count of specifc rows returned in cfquery

Question

I know you can get the count of how many items are returned in your query but... Question: Is it possible to get a recordcount of rows with a specific value for example...

<cfset totalReturned = myquery.recordcount>

<cfset totalReturnedComplete = myquery.recordcount (where status = "Complete")>

I know the above will not work but I am looking for something like the above code.

score 7 · Accepted Answer · answered May 13 '15 at 20:20

You have a couple of options. You could loop over the query with a counter that you increment when status = "Complete" or you could use a query of queries:

<cfquery name="mynewquery" dbtype="query">
    SELECT status, COUNT(*) AS status_cnt
      FROM myquery
     GROUP BY status
</cfquery>

Another way to do this, however, since you're using SQL Server, would be to modify your initial query (assuming you have access to do that) so the count where the status = "Complete" is returned, using SUM() as a window function:

<cfquery name="myquery" datasource="mydatasource">
    SELECT id, status
         , SUM(CASE WHEN status = 'Complete' THEN 1 ELSE 0 END) OVER ( ) AS complete_cnt
      FROM mytable
</cfquery>

<cfset totalReturned = myquery.recordcount />
<cfset totalReturnedComplete = myquery.complete_cnt />

See SQL Fiddle here.

score 2 · Answer 2 · answered May 13 '15 at 21:22

Generally, as has been answered it is best to do this type of counting as close to the DB as possible. However sometimes you need to count based on more dynamic parameters, or you don't have access to the DB server/service. In these cases you can either use a Query of Queries, or if using CF11+ you can use an arrayFilter.

An example of the arrayFilter would look something like:

... snipped, see link below for full runnable example
// convert query to array of structs
peopleArray = deserializeJSON(serializeJSON(people,"struct"));
// filter the array as needed
notMatt = peopleArray.filter( function(a){
    return a.firstname != 'Matt';
});

Then you could use the len() or arrayLen() function to get the count: i.e. notMatt.len()

The Query of Query option may be faster, haven't done any benchmarking, but that would look something like:

<cfquery dbtype="query" name="notMatt">
    SELECT count(*) as total 
    WHERE firstname != 'Matt'
</cfquery>

Then you'd just use noMatt.total. The below trycf gist shows both techniques (both in script form)

TryCF.com Examples

Get count of specifc rows returned in cfquery

2 Answers2