sql query with 2 subtotals ?

Databases
1

lets suppose one sql table with the fields date_of_invoice, amount_invoice, and customer_name
one customer can buy more than one thing in a day
I want to make in a single sql query (postgres prefered)
list all the purchases, sum each purchase for a customer each day, and the total purchased for each day for all customers.
I can get one or the other queries, but did not find how to combine them all

01-01-17  $100 customer1
01-01-17  $50  customer1
01-01-17  $150 total for customer1
01-01-17  $75  customer 2
01-01-17  $75  total for customer2
01-01-17  $225 total for day

thanks !

2 
SELECT 1 as category, customer, datefield, value
   from yourtable
union
select 2 as category, customer, datefield, sum(value) as value group by customer,datefield
 from yourtable
union 
select 3 as category, "*" as customer,datefield,sum(value) as value group by datefield
 from yourtable
order by category,datefield,customer

off the top of my head… 1=detail 2=sub customer by date, 3=subtotal by date

3

yes this should work indeed .
I was scratching my head with some commands like grouping sets, or rollup but did not find any solution …

4 
order by datefied,customer,category

should look more like my example.
thanks Dave.

5

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

6

I’ve read these, but did not find any working request for my need !
thanks Tobias.