Re:The SQL filter clause: selective aggregates
by jakobegger on Monday May 30, 2016 @01:07PM
(#11802146)
Assume that you have a table 'orders' similar to the following:
nr | date | product | quantity
---+------------+---------+----------
1 | 2016-05-29 | a | 2
2 | 2016-05-29 | a | 7
3 | 2016-05-29 | b | 3
4 | 2016-05-29 | a | 1
5 | 2016-05-30 | b | 5
6 | 2016-05-30 | a | 2
And you want to get daily order totals for each product.
Then you do:
SELECT
date,
SUM(quantity) FILTER(WHERE product='a') AS product_a,
SUM(quantity) FILTER(WHERE product='b') AS product_b,
SUM(quantity) AS total
FROM orders
GROUP BY date
ORDER BY date
Then you do:
And the result is