0

I'm trying to select a sequence of rows that exist between a specific date range and count the number of rows that have that date. However, there may be a date that has no entries, and therefore doesn't exist in the table.

The table contains rows with the following data:

2012-12-25
2012-12-25
2012-12-27
2012-12-27
2012-12-27
2012-12-28

As per this question, I've created a numbers table to generate the date range MySQL how to fill missing dates in range?

My current query fails near ('SELECT * FROM wp_paypal_logs WHERE STR_TO_DATE(wp_paypal_logs.created, '%Y-%m-%d')

SELECT x.ts AS timestamp,
          COALESCE(COUNT(SELECT * FROM wp_paypal_logs WHERE STR_TO_DATE(wp_paypal_logs.created, '%Y-%m-%d' ) = x.ts), 0) AS cnt
     FROM (SELECT DATE_FORMAT(DATE_ADD('2012-12-25', INTERVAL n.id - 1 DAY), '%Y-%m-%d') AS ts
             FROM numbers n
            WHERE DATE_ADD('2012-12-25', INTERVAL n.id - 1 DAY) <= '2012-12-31') x
LEFT JOIN TABLE wp_paypal_logs ON STR_TO_DATE(wp_paypal_logs.created, '%Y-%m-%d') = x.ts

How do I get my result set to look like this?

2012-12-25      2
2012-12-26      0
2012-12-27      3
2012-12-28      1
Community
  • 1
  • 1
Dave Kiss
  • 10,289
  • 11
  • 53
  • 75

1 Answers1

1

Not sure you need the COALESCE part since you are LEFT JOINing, if you count on a field from the left joined table that may do the trick (since it won't count NULLs):

SELECT 
   x.ts AS timestamp,
   COUNT(wp_paypal_logs.created) AS cnt
FROM (
    SELECT 
       DATE_FORMAT(DATE_ADD('2012-12-25', INTERVAL n.id - 1 DAY), '%Y-%m-%d') AS ts
    FROM numbers n
    WHERE DATE_ADD('2012-12-25', INTERVAL n.id - 1 DAY) <= '2012-12-31'
) x
LEFT JOIN wp_paypal_logs 
   ON STR_TO_DATE(wp_paypal_logs.created, '%Y-%m-%d') = x.ts
GROUP BY x.ts
Matt Dodge
  • 10,833
  • 7
  • 38
  • 58
  • Yeah, I'm pretty sure that this works as I'd hoped. The `COALESCE` felt weird in there to me too. Thanks for being smart! – Dave Kiss Jan 11 '13 at 17:32