I'm trying to calculate total revenue for every product and total revenue from orders with every product from my data set.

Sample date:

Expected result:

I'm using group by statement to calculate total revenue by product

select sum(revenue) from dataset group by item

but I can't calculate total from orders with products with subquery like this (I have total or all orders in column order_total):

select sum(revenue) 
     , (select sum(revenue) from dataset
        where order_id in (select distinct order_id from
                           dataset where item=item)) as order_total
from dataset 
group by item

Is this correct: dataset where item=item?

CodePudding user response：

My first thought was along these lines except I knew that SQL Server wouldn't like it because of the way it sums. It does work on Postgres though (with a lateral join. See link below):

select *
from
    (select distinct item from dataset) t1 cross apply
    (
    select
        sum(case when t2.item = t1.item then revenue end) as item_revenue,
        sum(revenue) as total_revenue
    from dataset t2
    group by order_id
    -- use a count if revenue > 0 isn't a reliable check
    having sum(case when t2.item = t1.item then revenue end) > 0
    ) agg;

The cleaner (and portable) answer would seem to be:

select t1.item,
    sum(case when t1.item = t2.item then t2.revenue end) as item_revenue,
    sum(t2.revenue) as total_revenue
from dataset t1 inner join dataset t2 on t2.orderid = t1.orderid
group by t1.item;

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=321a085216236465cc9b76a7f458c6f5

CodePudding user response：

Thank you @shawnt00, but I think subquery will be better for me in this case.

Query works with dataset aliases (ds1 and ds2):

select ds1.item, sum(revenue) as item_total
     , (select sum(revenue) from dataset
        where orderid in (select distinct orderid from
                           dataset as ds2 
                           where ds1.item=ds2.item)) as orders_with_item_total
from dataset as ds1
group by ds1.item

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=1bd20d6fb39029ce71cbeda87062f7e4