Difference of COUNT(*) between Query of Query(QoQ) and normal SQL Query

Before explaining the exact situation let me show you some code which we come across most of the time in our daily work life.

In above code here I'm displaying:

1. Total artists records.
2. COUNT of total no of artists by a condition which will return 0 record.
3. COUNT of total no of artists by a condition on Query of Query which also returns 0 record.

Lets see the output:

Here by first dump we get total no of records and in 2nd dump we are getting 1 record with ArtistCount column and value is 0, as the condition of the query doesn't matches any record. The 3rd dump where we again calculates the artist count but by Query of Query we get 0 no of rows.


I have tested the same thing in Oracle, SQL server and this built-in database with ColdFusion Admin application, all these databases returns 1 row when we evaluate COUNT(*) and the value of that field varies as per the total no records. i,e - it may be 0 or more than that.

But, if we consider the same situation by ColdFusion Query of Query then it returns 0 record for record count field if there is no matching record and returns record count if it is more than 0.

Conclusion:
Here I'm not trying to explain if it is an issue with QoQ. And I'm not sure if it would be correct to compare ColdFusion Query of Query with a database but we should remember this thing as in most of the time while evaluating COUNT(*) we use the field name and if we do the same for Query of Query then there might be situation where your code will fail.

Happy Coding! :)