Get the Subtotal, Total using Group By with Rollup in SQL Server

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.

(I will explain in next post).


The SQL Query for this,


select BCode, NoofLeaves,SUM(NoofBooks) TTL_Books

from ChequeDetails

where jobno='1214200644619 '

group by BCode,NoofLeaves

with rollup

order by BCode,NoofLeaves




Comments

Post a Comment

Popular posts from this blog

Use of Begin, Commit, Rollback Transactions in SQL Server

Image
The SQL Server provides very useful feature which is Begin, Commit, Rollback Transaction. When we use Begin Transaction before we use DML Queries, we can Commit or Rollback that Transaction after the confirmation. This is very useful if you update anything wrongly then you can rollback that transaction. For example,As shown below, I am trying to update the NodeID column data to 5 from 1. begin transaction update DownLoad_Data set NodeID=5 But after I updated, You can check whether you have been updated properly. But here, I realised I did not mention the Where clause. select * from DownLoad_Data So I have to rollback this transaction. For that I can use Rollback Transaction since I used Begin Transaction. Rollback transaction So again I changed the Query and run it. Begin transaction update DownLoad_Data set NodeID=5 where RecNo=1 Still you can check whether have been updated properly. If it is updated correctly then, Run the Commit Transaction to make all updates permanently. Commit...
Read more

SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time. Here I have created a simple Function for this. Create Function Trim(@mText varchar(MAX)) Returns varchar(MAX) AS Begin return LTRIM(RTRIM(@mText)) End You can run this function as shown below here, Select dbo.Trim(' Test ') So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.
Read more