Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to include zero when counting with a MySQL GROUP BY expression

I'd like to count the number events that occur on each day over the last month, but also include a count of zero when no events are found. Is that possible?

Here's what I'm starting from...

SELECT COUNT(*) AS count, 
DATE(usage_time_local) AS d 
FROM usages 
WHERE user_id=136 
AND DATE(usage_time_local) >= DATE('2011-04-24') 
AND DATE(usage_time_local) <= DATE('2011-05-24') 
GROUP BY DATE(usage_time_local);

UPDATE: Given the answer, I implemented a code solution by initializing a loop and then filling in the details.

  $dailyCount = array();
  for( $i=1; $i<=30; $i++ ) {
      $day = date('Y-m-d',(time()-($i*24*60*60)));
      $dailyCount[$day] = 0;
  }
  foreach( $statement as $row ) {
    $dailyCount[$row['d']] = $row['count'];
  }
like image 535
Greg Avatar asked Feb 25 '23 02:02

Greg


1 Answers

You can't do this with standard SQL queries - you'd be trying to group on a date(s) that doesn't exist in the table.

Standard workaround is to make a temporary table that contains the date range in sequential order with no gaps, join that against your table and do the count/aggregate as usual.

like image 182
Marc B Avatar answered Feb 26 '23 19:02

Marc B