Sure, this is a perfect usecase for GROUPING SETs!
Given the following schema and data:
[code]CREATE TABLE orders (
order_id integer PRIMARY KEY,
date date,
customer text,
amount numeric
);
INSERT INTO orders
VALUES (1, ‘2017-01-01’, ‘customer 1’, 100), (2, ‘2017-01-01’, ‘customer 1’, 50), (3, ‘2017-01-01’, ‘customer 2’, 75);
[/code]
You can get an output like the one requested by
SELECT
date,
sum(amount),
CASE GROUPING (date, customer, order_id)
WHEN 0 THEN
customer
WHEN 1 THEN
'Total for ' || customer
WHEN 3 THEN
'Total for day ' || date
END
FROM
orders
GROUP BY
GROUPING SETS (
(date, customer, order_id),
(date, customer),
(date))
ORDER BY
date,
customer,
order_id NULLS LAST
;
output
???????????????????????????????????????????????
? date ? sum ? case ?
???????????????????????????????????????????????
? 2017-01-01 ? 100 ? customer 1 ?
? 2017-01-01 ? 50 ? customer 1 ?
? 2017-01-01 ? 150 ? Total for customer 1 ?
? 2017-01-01 ? 75 ? customer 2 ?
? 2017-01-01 ? 75 ? Total for customer 2 ?
? 2017-01-01 ? 225 ? Total for day 2017-01-01 ?
???????????????????????????????????????????????
Or simpler and more general with a ROLLUP:
SELECT
date,
customer,
order_id,
sum(amount)
FROM
orders
GROUP BY
ROLLUP (date, customer, order_id)
ORDER BY
date,
customer,
order_id
;
????????????????????????????????????????????
? date ? customer ? order_id ? sum ?
????????????????????????????????????????????
? 2017-01-01 ? customer 1 ? 1 ? 100 ?
? 2017-01-01 ? customer 1 ? 2 ? 50 ?
? 2017-01-01 ? customer 1 ? [NULL] ? 150 ?
? 2017-01-01 ? customer 2 ? 3 ? 75 ?
? 2017-01-01 ? customer 2 ? [NULL] ? 75 ?
? 2017-01-01 ? [NULL] ? [NULL] ? 225 ?
? [NULL] ? [NULL] ? [NULL] ? 225 ?
????????????????????????????????????????????
Further reading: GROUPING SETS, CUBE, and ROLLUP and GROUPING function