Conditional group by in SQL Server

In this post, we are going to learn how we can apply a conditional GROUP BY clause on a column, based on the values of another column. Assume that we have a table named tbl_EmpSaleDetail which contains the sales records for each employee. Let’s have a look at the table data.

In the above table, we have these columns:

EmpName – Stores the name of the employee

SaleDate – Date of sale

SaleAmount – Amount of the sale

IsActive – Indicates whether the employee is active or not.

Now, we need this output.

In this output, we can see that all the data of inactive employees have been aggregated to a single row labeled as “–Inactive Employees Sales–” (Highlighted in red). However, the sum of the sales of the active employees are aggregated individually. Before writing the conditional group by query, lets create the sample table with dummy data using this script.

IF OBJECT_ID('dbo.tbl_EmpSaleDetail') IS NOT NULL
	DROP TABLE dbo.tbl_EmpSaleDetail
GO
--Create a sample table now
CREATE TABLE dbo.tbl_EmpSaleDetail
(	
	EmpName VARCHAR(256),
	SaleDate DATETIME,
	SaleAmount DECIMAL(18,2),
	IsActive BIT
)
GO

Conditional GROUP BY query – SQL Server

There could be several ways to achieve the above output using T-SQL queries. However, in this post, we will be using a conditional group by clause in order to get the required output. This is the script to generate the required output data.

SELECT 
CASE WHEN IsActive = 1 THEN EmpName ELSE '--Inactive Employees Sales--' END AS EmpName, 
SUM(SaleAmount) AS TotalSale 
FROM dbo.tbl_EmpSaleDetail
GROUP BY CASE WHEN IsActive = 1 THEN EmpName ELSE '--Inactive Employees Sales--' END
ORDER BY EmpName
GO

In the above query, we have used a CASE expression in GROUP BY clause to dynamically convert the inactive employee names to a fixed string value “–Inactive Employees Sales–“. We have used the same CASE expression in the SELECT statement also. Otherwise it will throw an error message “Column ‘dbo.tbl_EmpSaleDetail.EmpName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.”

If we want to show this value at top or at the bottom, we can use a similar trick as mentioned here.

Thanks for the reading. Please share your input in comments.