Using ROLLUP for subtotals

rollup.sql

/*
    Using ROLLUP for subtotals

    DuckDB version: 1.0.0

    Bill Wallis, 2024-08-04
*/


select version();


create or replace table line_items (
    region_id      integer,
    invoice_id     integer,
    product_id     integer,
    sales_price    integer,
    sales_quantity integer,
    primary key (region_id, invoice_id, product_id)
);
insert into line_items
values
    (1, 1, 202, 150, 3),
    (1, 1, 203, 120, 7),
    (1, 1, 301, 200, 5),
    (1, 1, 302, 300, 2),
    (1, 2, 101, 250, 4),
    (1, 2, 302, 500, 6),
    (1, 3, 102, 400, 3),
    (1, 3, 201,  40, 6),
    (1, 3, 202, 800, 7),
    (1, 3, 203, 600, 8),
    (1, 3, 301, 700, 1),
    (1, 3, 302,  50, 9),
    (1, 3, 303,  60, 5),
    (1, 4, 103,  80, 8),
    (1, 4, 201, 210, 4),
    (1, 4, 303,  70, 7),
    (2, 5, 302, 220, 6),
    (2, 6, 203, 230, 2),
    (2, 7, 101, 250, 3),
    (2, 7, 102, 530, 6),
    (2, 7, 203, 510, 7),
    (2, 7, 301, 520, 8),
    (2, 7, 302, 240, 5),
    (2, 8, 202, 540, 4),
    (2, 8, 203,  90, 3),
    (2, 8, 301, 550, 5),
    (2, 8, 302, 100, 4),
    (2, 9, 101, 110, 2),
    (2, 9, 102, 120, 5),
    (2, 9, 301, 130, 6)
;


select
    case grouping_id(region_id, invoice_id)
        when 3 then 'Grand Total'
        when 1 then '  Region Total'
        when 0 then '    Invoice Total'
    end as grain,

    region_id,
    invoice_id,
    sum(sales_price * sales_quantity) as total_sales
from line_items
group by rollup (region_id, invoice_id)
order by
    region_id nulls first,
    invoice_id nulls first
;

This is part of the following LinkedIn post:

• https://www.linkedin.com/posts/bill-wallis_sql-analyticsengineering-dataengineering-ugcPost-7225817045660028928-XQPs