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)