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.