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. But if you want get the subtotals; you can use rollup clause with Group By clause. Rollup clause would give the subtotal specified columns whatever you mentioned in group by clause. 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 rollup clause with Group By clause. When we use rollup clause it would give total of NoofBooks for each BCode and NoofLeaves, subtotal for each BCode and Grand Total of NoOfBooks Here you can notice that when its give the subtotal for each BCode, NoofLeaves columns filled NULL values as it does not have any values to return. But we can avoid showing as NULL. The SQL Query for this, {codecitation class="brush: sql; gutter: true;" width="500px"} select BCode, NoofLeaves,SUM(NoofBooks) TTL_Books from ChequeDetails where jobno='1214200644619 ' group by BCode,NoofLeaves with rollup order by BCode,NoofLeaves {/codecitation} You can replace the NULL values with your own Text. For that you will have to use the Grouping() function. 
{codecitation class="brush: sql; gutter: true;" width="500px"}
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 rollup order by BCode,NoofLeaves {/codecitation} Thank you Mukuran |