Grouping and Rollup SQL aggregation

grouping_sets_and_rollup.sql

-- Reference
-- https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

-- TODO: add more detail, this is syntax reference for me 

Select fname, food, sum(total)
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by grouping sets ((fname, food), ()) 


go

Select fname, food, sum(total)
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by fname, food with rollup


go

Select fname, food, sum(total)
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by  food, fname with rollup

go



Select fname, food, sum(total)
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by grouping sets ((fname, food), ()) 

go


Select fname, food, sum(total) as total
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by grouping sets ((fname, food), (food), ()) 

go

Select fname, food, sum(total) as total
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by grouping sets ((fname, food), (fname), ()) 

go


Select fname, food, sum(total) as total
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by rollup(fname), food

go

Select fname, food, sum(total) as total
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by rollup(food, fname)

go

Select fname, food, sum(total) as total
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by rollup(fname, food)  -- ORDER MATTERS!

go

Select fname, food, sum(total) as total
From lateral(
    values
    ('Bob', 'Pies', 3), 
    ('Charlie', 'Pies', 1), 
    ('Bob', 'Cakes', 2), 
    ('Charlie', 'Cakes', 6),
    ('Peter', 'Fizzy Pop', 2), 
    ('Charlie', 'Fizzy Pop', 2), 
    ('Bob', 'Fizzy Pop', 2)
) as T(fname, food, total)
Group by cube(food, fname)