Grouping Function in sql server.

Grouping function:

This function is used to determine whether or not the value generated for a col by super aggregate is Null.

This function takes colname as argument and returns 1, if the value generated for that column by super aggregate is Null and otherwise return zero.

select case grouping(deptno)

when 1 then 'All Depts'

else

cast(deptno as varchar)

end as Deptno,

case grouping(job)

when 1 then 'All Jobs'

else

job

end as job, sum(Sal) as totalsalary from emp group by deptno, job with cube

Note: no typecast for job because job col is varchar.