Skip to content
Advertisement

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:

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

Advertisement

Answer

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

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)

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement