SQL Assignment – Module 4

 Q. What is the difference between count(0), count(1), and count(*) in MySQL/SQL?

Ans. The difference in “count(<whatever>)” is mostly if you have

count(<fixed value>)

versus

count(<expression>) 

select count(<fixed value>) - as long as it isn’t a literal NULL - ends up being the same as count(*). And no, 0 isn’t special; select count(0) is the same as select count(1).

select count(<expression>) is actually interesting. It adds 1 for each row where <expression> evaluates to non-NULL, and 0 otherwise. So, you can do the following:

select count(somecol) from sometab; 

returns the total count of records where somecol IS NOT NULL

select count(if(somecol > 50, 1, NULL)) from sometab; 

returns the total count of records where somecol is > 50

The above may seem silly, until you realize that you can use count() multiple times, ie

select count(if(somecol > 50, 1, NULL)), 
       count(if(othercol between <date1> and <date2>, 1, NULL)), 
       count(if(yetanothercol between col17 and col19, 1, NULL), 
       count(if(col21 in ('foo','bar','baz','blah'),1,NULL)),  
       ... and hundreds of others ... 
  from sometab; 

(yes, the if() function is MySQL-specific, but you can implement similar logic with case/when, etc)

This is an easy, highly-performant way to do reports that involve a whole bunch of counting with different counting conditions without needing to do numerous passes through the table; the added “count()s” after the first one are basically “free” in terms of performance.