Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at InVision Office 2012 (New York City) with:

ColdFusion Query Of Queries Odd COUNT(*) Behavior

By Ben Nadel on
Tags: ColdFusion

If you ever run a COUNT(*) aggregate in a SQL SELECT statement, you will know that it returns the number of records that match your select criteria. If 14 records match, it returns 14. If zero records match, it returns zero. This behavior, however, is corrupted when it comes to ColdFusion query of queries.

As always, let's start by building a query against which to run tests:

  • <!--- Set up a query for girls. --->
  • <cfset qGirls = QueryNew( "id, name, hotness, hair" ) />
  •  
  • <!--- Add some rows to the query. --->
  • <cfset QueryAddRow( qGirls, 4 ) />
  •  
  • <!--- Set data values. --->
  • <cfset qGirls[ "id" ][ 1 ] = 1 />
  • <cfset qGirls[ "name" ][ 1 ] = "Libby" />
  • <cfset qGirls[ "hotness" ][ 1 ] = 8.5 />
  • <cfset qGirls[ "hair" ][ 1 ] = "Brunette" />
  •  
  • <cfset qGirls[ "id" ][ 2 ] = 2 />
  • <cfset qGirls[ "name" ][ 2 ] = "Yuu" />
  • <cfset qGirls[ "hotness" ][ 2 ] = 7.5 />
  • <cfset qGirls[ "hair" ][ 2 ] = "Black" />
  •  
  • <cfset qGirls[ "id" ][ 3 ] = 3 />
  • <cfset qGirls[ "name" ][ 3 ] = "Kim" />
  • <cfset qGirls[ "hotness" ][ 3 ] = 7.5 />
  • <cfset qGirls[ "hair" ][ 3 ] = "Brunette" />
  •  
  • <cfset qGirls[ "id" ][ 4 ] = 4 />
  • <cfset qGirls[ "name" ][ 4 ] = "Molly" />
  • <cfset qGirls[ "hotness" ][ 4 ] = 10.0 />
  • <cfset qGirls[ "hair" ][ 4 ] = "Brunette" />

Now that we have our girls set up, let's query our query to find the number hot girls we have. For this test, our definition of hot will be those girls with a hotness rating of 8 or higher.

  • <!---
  • Now, get the number of girls that have a hotness
  • over 8. That's really hot.
  • --->
  • <cfquery name="qHotGirls" dbtype="query">
  • SELECT
  • COUNT( * ) AS girl_count
  • FROM
  • qGirls
  • WHERE
  • hotness > 8
  • </cfquery>

As you can see, all we are doing is selecting the COUNT( * ) on the query. Dumping out the results, we find we have two hot girls, just as can be expected:


 
 
 

 
CFDump qHotGirls - COUNT(*) Demo  
 
 
 

Now, let's query our query to find the number of not-so-hot girls. For this test, our definition of not-so-hot will be those girls with a hotness rating less than 5.

  • <!---
  • Now, get the number of girls that have a hotness
  • under 5. That's not so hot.
  • --->
  • <cfquery name="qNotSoHotGirls" dbtype="query">
  • SELECT
  • COUNT( * ) AS girl_count
  • FROM
  • qGirls
  • WHERE
  • hotness < 5
  • </cfquery>

Again, we are just getting the number of girls as a COUNT aggregate. As we had no not-so-hot girls in the original query, we would expect to get a record with the value zero (0). Dumping out the results, though, you will see that in fact, we have no records at all:


 
 
 

 
CFDump qNotSoHotGirls - COUNT(*) Demo  
 
 
 

This is bizarre and can be a hassle in your programming. Now, just to make sure that I am not going crazy, I am going to do a control test against a regular SQL database to see how it works:

  • <cfquery name="qControlTest" datasource="...">
  • SELECT
  • COUNT( * ) AS record_count
  • FROM
  • blog_entry
  • WHERE
  • <!--- This ensures that no records will be returned. --->
  • 1 = 0
  • </cfquery>

This query is designed to NEVER return anything but zero as the count. And, from the following dump, you will see that this is in fact how a COUNT aggregate in the SQL DB works:


 
 
 

 
CFDump qControlTest - COUNT(*) Demo  
 
 
 

Now, as I mentioned, this can be somewhat irritating to deal with in the code. The easiest solution to work with is to simply check to see if any records were returned, and, if no records were returned, create one manually with a default value:

  • <!--- Check to see if any records were returned. --->
  • <cfif NOT qNotSoHotGirls.RecordCount>
  •  
  • <!--- Set default values for aggregates. --->
  • <cfset QueryAddRow( qNotSoHotGirls, 1 ) />
  • <cfset qNotSoHotGirls[ "girl_count" ][ 1 ] = 0 />
  •  
  • </cfif>

This way, you can carry on with the programming and not have to take any special considerations just because you were performing a query of queries rather than querying a database.



Reader Comments

this might be hugely off topic, but I have made a search form with several dropdown boxes, I would like that if no results match the search (if the query returns no records) the user gets a line of text saying something in the lines of "Your search returned no results, please try changing your criteria", could you possibly let me know how I would go about doing this?

Ben... while this approach seems to work for single counts, how would you recommend a query-of-queries utilizing grouping that feeds a chart? For instance I have a huge query to compiles all active support cases for our staff then a query of queries that generates individual case counts per staff member for a chart of resolved vs. active cases.

here sel_cases is a huge sql to munge together all cases in the system so I left it out of the post to get to the meat...

<cfquery dbtype="query" name="casesbystaffactive">
select sFullName,count(sFullName) As Counter from sel_cases WHERE cIsActive='1' GROUP BY sFullname;
</cfquery>
<cfquery dbtype="query" name="casesbystaffresolved">
select sFullName,count(sFullName) As Counter from sel_cases WHERE cIsActive='0' GROUP BY sFullname;
</cfquery>
<cfchart title="Active vs Resolved Caseload">
<cfchartseries type="bar" query="casesbystaffactive" valueColumn="Counter" itemColumn="sFullName"/>
<cfchartseries type="bar" query="casesbystaffresolved" valueColumn="Counter" itemColumn="sFullName"/>
</cfchart>

What I have found in this approach is that if sFullName has no results obviously their counter should be zero but is not. What CF then does really irritates and baffles me. The chart seems to reflect some random number of cases a person that has no resolved cases.. it appears to just make up a number.

Off the cuff I guess I could use cfdata in a loop for the series and manually set the zero values, etc. but what a hack. Just wondering if there is a slick way to default the counter to zero at the query layer.

To follow up.. what this does is not generate a random number.. the bar chart actually creates a value halfway between the values on both sides of the sFullName. So if user1 has 10 cases and user3 has 20 the chart assumes that with no value user2 should have a bar of 15 ... whacky.

@Charles,

To be honest, I have not done too much work with charting in ColdFusion. I think the last time I actually generated a chart was like 4 years ago :) Typically when dealing with queries that may result in a non-count value, I'll use the val() method to make sure it at least returns a zero:

val( myQuery.someCount )

This way, at least I can rest-assured that I'm going to get a numeric value.

But, if you need to pass that into a "black box" that expects a query, I think you'll just have to manually update the query after you get it.