- Code: Select all
id | mail_date1 | mail_date2 | mail_date3
------------------------------------------
1 | 2015-01-01 | 2015-01-08 | null
2 | 2015-01-01 | 2015-01-02 | 2015-01-02
3 | 2015-01-01 | 2015-01-05 | 2015-01-08
I'm trying to get a working query that returns a count of records for each day on those 3 date fields:
- Code: Select all
date | count(mail_date1) | count(mail_date2) | count(mail_date3)
2015-01-01 | 3 | 0 | 0
2015-01-02 | 0 | 1 | 1
2015-01-03 | 0 | 0 | 0
2015-01-04 | 0 | 0 | 0
2015-01-05 | 0 | 1 | 0
2015-01-06 | 0 | 0 | 0
2015-01-07 | 0 | 0 | 0
2015-01-08 | 0 | 1 | 1
The only solution I find is to use unions:
- Code: Select all
SELECT *
FROM (
SELECT
dates.dt,
count(sent.id) AS count1,
0 AS count2,
0 AS count3
FROM (
SELECT generate_series('2015-01-01' :: TIMESTAMP, '2015-01-31', '1 day') AS dt
) AS dates
LEFT OUTER JOIN mail sent ON dates.dt = DATE(sent.mail_date1)
GROUP BY dates.dt
UNION
SELECT
dates.dt,
0,
count(remind.id),
0
FROM (
SELECT generate_series('2015-01-01' :: TIMESTAMP, '2015-01-31', '1 day') AS dt
) AS dates
LEFT OUTER JOIN mail remind ON dates.dt = DATE(remind.mail_date2)
GROUP BY dates.dt
UNION
SELECT
dates.dt,
0,
0,
count(thank.id)
FROM (
SELECT generate_series('2015-01-01' :: TIMESTAMP, '2015-01-31', '1 day') AS dt
) AS dates
LEFT OUTER JOIN mail thank ON dates.dt = DATE(thank.mail_date3)
GROUP BY dates.dt
) AS T
ORDER BY t.dt
But this is not working correctly (when I add the third union it adds an extra line to the result instead of combining the result to the previous unions)
And I think that there should be a more allegant solution to this problem.