How to give names to group sets in SQL

You have a group set query in SQL that you are using to give sum total of certain numbers. The problem is where the row calculates the sum total, the cell values in the corresponding colums say ‘NULL’. How to change ‘NULL’ to something more meaninful, that is ‘Total’, ‘Sum’, ‘Sales in United States’, etc

It is really easy. Use ISNULL() function.

Select Employee, Year, Month, count(*)  as NoOfLeaves from Employee
group by Employee, Year, Month

This query is standard query that will give you No of Leaves by each employee in all months. Now we are also interested in total leave in the year, for that we will use group sets

Select Employee, Year, Month, count(*)  as NoOfLeaves from Employee
WHERE EmployeeID= 100
group by Employee, Year, Month

The problem here is the last column will give us the required number of leav1es in the year but it would say what it is in the EmployeeID column. In this case we know that it is total no of leaves in an year by that employee, so lets call it ‘Total’. We will modify the query as

Select IsNULL(Employee, 'Total') as Employee, Year, Month, count(*)  as NoOfLeaves from Employee
WHERE EmployeeID= 100
group by Employee, Year, Month
Advertisements
This entry was posted in SQL Server 2008 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s