CODEWITHSUNDEEP
mysql
Dave Kiss
Dave Kiss

Reputation: 10495

MySQL - Selecting the number of rows in date range

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

Upvotes: 0

Views: 796

Answers (1)

Matt Dodge
Matt Dodge

Reputation: 11152

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

Upvotes: 1

Related Questions