Skip to content
Advertisement

How to remove subtotals from groupby OLAP Cube?

select Field1,DatePart(Year,aDate) aDate,count(distinct Employee) EmployeeCount from  dbo.myTable with (nolock)   group by cube(Field1,DatePart(Year,aDate))  order by EmployeeCount desc

output

Field aDate  EmployeeCount
 01-    2012 27166
 NULL   2012 27166
 NULL   NULL 27166 

The filter shows field is not null. Why am I getting a Field NULL as part the EmployeeCount output when using cube?

I figured it out. Cube includes subtotal and totals where the column is null

Is there an easy way to remove the rollup values in the cube results

Advertisement

Answer

use group by grouping sets, it allows you to select the aggregation levels or sets

select Field1,DatePart(Year,aDate) Year,count(distinct Employee) EmployeeCount from  dbo.aTable with (nolock)  
group by grouping sets((Field1,DatePart(Year,aDate))

this is equivalent to
group by Field1, DatePart(Year,aDate)

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