---

Search posts under SQL Server:

---

Filter in Group by in SQL Server

How to filter result returned from Group by clause in SQL Server?

To filter the result returned from the GROUP BY clause, we use HAVING clause. Remember that we can’t use WHERE clause to filter records with the GROUP BY clause.

SELECT
              LastName,
              SUM(NetSalary) AS NetSalary,
              SUM(PPFDeduction) AS PPFDeduction
              FROM viewPersonalAccounts
GROUP BY LastName
HAVING LastName = 'Narayan'

In the above statement, we have filtered the result of GROUP BY having LastName = ‘Narayan’.

Similarly, we can also filter by the SUM of either NetSalary or PPFDeduction.

Important

Only those fields are allowed in the HAVING clause that is the part of SELECT statement. If SELECT statement has Aggregate function with column names, HAVING clause must have that Aggregate functions with the column name.

Like, below statement will not work

SELECT
              LastName,
              SUM(NetSalary) AS NetSalary,
              SUM(PPFDeduction) AS PPFDeduction
              FROM viewPersonalAccounts
GROUP BY LastName
HAVING PPFDeduction > 10000

As the SELECT statement has SUM aggregate functions with the PPFDeduction but HAVING is directly trying to compare the value of PPFDeduction column. To solve this problem, we can have SUM function in the PPFDeduction column as well after HAVING clause.