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.

Answer 1

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.