Introduction In Microsoft SQL Server normally when you use aggregation function you must use Group By clause to get the Total/Average So on. But by using Group by you can get the aggregation only (Total/Avg...etc) based on the columns which you are giving in Group By clause. Explanation about Cube But if you want get the subtotals; you can use Cube, rollup clause with Group By clause. Cube clause would give the subtotal for all possible matching like summary for all specified columns whatever you mentioned in group by clause Main different between Cube and Rollup is, Group BY with Cube clause would give All the Subtotal as summary. For an example : In the shown example, I need to get the total and subtotal of NoofBooks based on BCode and NoofLeaves but I can only get total based on those columns if I use Group By clause only. For that I can use cube clause with Group By clause. When we use cube clause it would give total of NoofBooks for each BCode and NoofLeaves, subtotal for each BCode , Give the Subtotal summary will be shown separately (This is main different from rollup) and Grand Total of NoOfBooks The SQL Script for this, {codecitation class="brush: sql; gutter: true;" width="650px"} select BCode, NoofLeaves,SUM(NoofBooks) TTL_Books from ChequeDetails where jobno='1214200644619 ' group by BCode,NoofLeaves with cube order by BCode,NoofLeaves {/codecitation} Result 
You can replace the NULL values with your own Text. For that you will have to use the Grouping() function. Script to replace Null values: {codecitation class="brush: sql; gutter: true;" width="650px"} select Case Grouping(BCode) When 1 Then 'All BCode' Else BCode End As BCode , Case Grouping(NoofLeaves) When 1 Then 111 Else NoofLeaves End As NoofLeaves,SUM(NoofBooks) TTL_Books from ChequeDetails where jobno='1214200644619 ' group by BCode,NoofLeaves with Cube order by BCode,NoofLeaves {/codecitation} Thank you Murukan |