Created
March 20, 2015 15:16
-
-
Save JPrevost/929357876b3172a5d92a to your computer and use it in GitHub Desktop.
Sum two values of a single cell, group by dates, and return 0 for dates where there are no results
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT year, month, sum(total) as total, sum(deliverCount) as delivered, sum(rejectCount) as rejected | |
FROM ( | |
SELECT extract(year from created) AS year, extract(month from created) AS month, count(*) as total, | |
sum(CASE WHEN action = 'deliver' then 1 else 0 end) deliverCount, | |
sum(CASE WHEN action = 'discard' then 1 else 0 end) rejectCount | |
FROM transfer | |
WHERE subscriber_id = 1 | |
AND created BETWEEN '2015-01-01 0:0' AND '2016-01-01 0:0' | |
GROUP BY extract(year from created), extract(month from created) | |
UNION | |
SELECT extract(year FROM generate_series('2015-01-01 0:0'::TIMESTAMP, '2015-12-31 0:0'::TIMESTAMP, INTERVAL '1 month')) AS year, | |
extract(month FROM generate_series('2015-01-01 0:0'::TIMESTAMP, '2015-12-31 0:0'::TIMESTAMP, INTERVAL '1 month')) AS month, | |
0, 0, 0 | |
) AS t | |
GROUP BY year, month | |
ORDER BY year, month; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment