How to eliminate blanks from SQL Summary Row with Totals with ROLLUP

I have the following SQL in Sql server 2016. I am trying to capture Totals of Site in Column and then Sum total in Row with ROLLUP. However, I am getting Nulls as well. I am providing sample data here:

            create table #tempSite
                (
                Report_Date smalldatetime null,
                Code varchar(10) null,
                DepSite varchar(10) null
                )

                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'UVXYZ', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'UVXYZ', 'NC')

                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'PQRST', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'PQRST', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'UVXYZ', 'CA')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'PQRST', 'CA')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'UVXYZ', 'MD')

                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'PQRST', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'PQRST', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'UVXYZ', 'CA')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'PQRST', 'CA')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'UVXYZ', 'MD')

                SELECT Report_Date, Code, DepSite, count(DepSite) TOT_SITE
                FROM #tempSite  
                WHERE Code ='ABCDE'in 
                and Report_Date in ('2020-05-22', '2020-05-24')
                group by ROLLUP(Report_Date, Code, DepSite)

How can I eliminate NULLS? Is there a way to get results like:

Report_Date         Code    DepSite TOT_SITE
2020-05-22 00:00:00 ABCDE   FL      2
2020-05-22 00:00:00 ABCDE   NC      3
2020-05-22 00:00:00 ABCDE   SC      3
2020-05-22 00:00:00 NULL    NULL    8
2020-05-24 00:00:00 ABCDE   FL      1
2020-05-24 00:00:00 ABCDE   NC      3
2020-05-24 00:00:00 ABCDE   SC      3
2020-05-24 00:00:00 NULL    NULL    7
NULL                NULL    NULL    15

Answer

You can filter the GROUP BY via a HAVING clause using the GROUPING function:

SELECT Report_Date, Code, DepSite, count(DepSite) TOT_SITE
FROM #tempSite  
WHERE Code ='ABCDE'
and Report_Date in ('2020-05-22', '2020-05-24')
group by ROLLUP(Report_Date, Code, DepSite)
having grouping(DepSite) = 0
or grouping (Code) = 1

GROUPING is 1 when the column is being totalled in that row as part of the ROLLUP. So here you check that either the final column (DepSite) is not being totalled by the ROLLUP (and is thus a standard aggregated row output by the GROUP BY) or that Code is being totalled and thus that DepSite must be too (because you are doing a ROLLUP not a CUBE and Code precedes DepSite)

Advertisement

Answer