Skip to content

Instantly share code, notes, and snippets.

@JPrevost
Created March 20, 2015 15:16
Show Gist options
  • Save JPrevost/929357876b3172a5d92a to your computer and use it in GitHub Desktop.
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
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