Search code examples
sqlsql-servert-sqlrollup

Grouping Sets, Roll-up but with multiple columns


I have a query that produces the following results. The problem I'm having is how can I create 3 rows at the bottom showing the totals for >=14 days, and 1-13 days followed by an overall total.

select
[Period],
[Person],
count(*) as NumberOfRecords,
sum([Amount]) as [Amount]

If I do the below and exclude the Person I get an overall total, but I need the Person to show also:

GROUP BY GROUPING SETS(([[Period]),());

How could this be done?

enter image description here

Create table #temp
(
    Period varchar(50),
    Person varchar(100),
    NumberOfRecords int,
    Amount money
)

insert into #temp
(
    Period,
    Person,
    NumberOfRecords,
    Amount
)
select
    '>= 14 days','',3,100
union all
select
    '>= 14 days','John Smith',32,200
union all
select
    '>= 14 days','Joe Bloggs',50,400
union all
select
    '>= 14 days','Jane Doe',52,750
union all
select
    '>= 14 days','Barry Knight',46,1000
union all
select
    '1-13 days','Bob the Builder',331,7500

Solution

  • You can use ROLLUP for subtotals.

    SELECT
        CASE WHEN GROUPING(Period) = 1 THEN 'Total' ELSE Period END Period, 
        Person, SUM(NumberOfRecords) NumberOfRecords, 
        SUM(Amount) Amount 
    from #temp
    GROUP BY ROLLUP ((Period),(Period, Person))
    ORDER BY GROUPING(Period), GROUPING(Person)
    

    Result:

    Period               Person               NumberOfRecords Amount
    -------------------- -------------------- --------------- ---------------------
    >= 14 days                                3               100.00
    >= 14 days           Barry Knight         46              1000.00
    >= 14 days           Jane Doe             52              750.00
    >= 14 days           Joe Bloggs           50              400.00
    >= 14 days           John Smith           32              200.00
    1-13 days            Bob the Builder      331             7500.00
    1-13 days            NULL                 331             7500.00
    >= 14 days           NULL                 183             2450.00
    Total                NULL                 514             9950.00