SQL Server GROUP BY, GROUP BY ALL and HAVING Queries

SQL Server GROUP BY

In SQL Server, GROUP BY clause is used to summarizes the records into groups in the query using aggregate functions.

HAVING clause is used to further filter and restricts the result set to generate reports based on the condition.

GROUP BY Syntax

SELECT column_name1[, column_name2, ....]
FROM table_name
WHERE condition
[GROUP BY [ALL] column_name | expression 
[, column_name | expression ...]
HAVING filter_condition

ALL keyword is used to include the groups which do not meet the filter condition.

Employee Table

Let us consider the example 'Employee' database table.

GROUP BY Example

To group by column Country and getting minimum and maximum salary for each groups.

SELECT Country, MIN(Salary) 'Minimum Salary', MAX(Salary) 'Maximum Salary'
FROM Employee
WHERE Salary > 4000
GROUP BY Country

GROUP BY Based on Multiple Columns Example

To group by column Country, DeptId and getting minimum and maximum salary for each groups.

SELECT Country, DeptId, MIN(Salary) 'Minimum Salary', MAX(Salary) 'Maximum Salary'
FROM Employee
WHERE Salary > 5000
GROUP BY Country, DeptId

GROUP BY with HAVING Example

To group by column Country and getting average salary for each groups and filters the groups which are not having average salary is greater than 7000.

SELECT Country, AVG(Salary) 'Average Salary'
FROM Employee
WHERE Salary > 4000
GROUP BY Country
HAVING AVG(Salary) > 7000

GROUP BY aLL Example

To group by column Country, getting average salary for each groups and display's all group's in the result set.

Display's average salary as NULL value if not calculated for any groups.

SELECT Country, AVG(Salary) 'Average Salary'
FROM Employee
WHERE Salary >8000
GROUP BY ALL Country