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:
x
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
Advertisement
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
)