Calculating Multiple Averages Across Different Parts Of The Table?

I have the following transactions table: customer_id purchase_date product category department quantity store_id 1 2020-10-01 Kit Kat

Solution 1:

How about using “union all” as below -

Select store_id, 'product'as average_level_type,product as id, sum(quantity) as total_quantity,
Count(distinct customer_id) as unique_customer_count, sum(quantity)/count(distinct customer_id) as average
from transactions
where quantity >0groupby store_id,product
UnionallSelect store_id, 'category'as average_level_type, category as id, sum(quantity) as total_quantity,
Count(distinct customer_id) as unique_customer_count, sum(quantity)/count(distinct customer_id) as average
from transactions
where quantity >0groupby store_id,category
UnionallSelect store_id, 'department'as average_level_type,department as id, sum(quantity) as total_quantity,
Count(distinct customer_id) as unique_customer_count, sum(quantity)/count(distinct customer_id) as average
from transactions
where quantity >0groupby store_id,department;

If you want to avoid using union all in that case you can use something like rollup() or group by grouping sets() to achieve the same but the query would be a little more complicated to get the output in the exact format which you have shown in the question.

EDIT : Below is how you can use grouping sets to get the same output -

Select store_id,
       casewhen G_ID = 3then'product' when G_ID = 5then'category'when G_ID = 6then'department' end As average_level_type,casewhen G_ID = 3then product 
            when G_ID = 5then category
            when G_ID = 6then department endAs id,
       total_quantity,
       unique_customer_count,
       average
from            
    (select store_id, product, category, department, sum(quantity) as total_quantity, Count(distinct customer_id) as unique_customer_count, sum(quantity)/count(distinct customer_id) as average, GROUPING__ID As G_ID
    from transactions
    groupby store_id,product,category,department
    grouping sets((store_id,product),(store_id,category),(store_id,department))
    ) Tab
orderby2    
;