GROUP BY
The GROUP BY clause is used to group rows that have the same values in one or more columns.
What is GROUP BY?
The GROUP BY clause in SQL Server is used to arrange rows into groups based on one or more columns.
It is mainly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN() and MAX().
GROUP BY takes similar records and puts them into logical groups so that we can perform calculations on each group.
Why Do We Use GROUP BY?
GROUP BY is useful when we want summarized information instead of displaying every individual row.
- Find the number of employees in each department.
- Calculate total salary department-wise.
- Calculate average salary for each department.
- Find minimum and maximum salary in each department.
- Generate summary reports from large tables.
GROUP BY Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;
Example Table
Consider the following Employees table:
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Rahul | IT | 50000 |
| 2 | Priya | HR | 40000 |
| 3 | Amit | IT | 60000 |
| 4 | Neha | HR | 45000 |
| 5 | Rohit | Sales | 35000 |
Basic GROUP BY Example
Suppose we want to find the number of employees in each department.
SELECT Department,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
Output
| Department | EmployeeCount |
|---|---|
| HR | 2 |
| IT | 2 |
| Sales | 1 |
How GROUP BY Works
In the above example, SQL Server looks at the Department column and creates groups for matching values.
IT Group: Rahul, Amit
HR Group: Priya, Neha
Sales Group: Rohit
Then COUNT() calculates the number of records in every group.
GROUP BY with SUM()
We can use GROUP BY with SUM() to calculate the total salary for every department.
SELECT Department,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;
Result
| Department | TotalSalary |
|---|---|
| HR | 85000 |
| IT | 110000 |
| Sales | 35000 |
GROUP BY with AVG()
AVG() calculates the average salary for every department.
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;
GROUP BY with MIN()
MIN() returns the lowest salary in each department.
SELECT Department,
MIN(Salary) AS MinimumSalary
FROM Employees
GROUP BY Department;
GROUP BY with MAX()
MAX() returns the highest salary in each department.
SELECT Department,
MAX(Salary) AS MaximumSalary
FROM Employees
GROUP BY Department;
GROUP BY with Multiple Aggregate Functions
We can use multiple aggregate functions in the same query.
SELECT
Department,
COUNT(*) AS EmployeeCount,
SUM(Salary) AS TotalSalary,
AVG(Salary) AS AverageSalary,
MIN(Salary) AS MinimumSalary,
MAX(Salary) AS MaximumSalary
FROM Employees
GROUP BY Department;
GROUP BY with HAVING
The HAVING clause is used to filter groups after GROUP BY has been applied.
SELECT Department,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 1;
WHERE filters individual rows, while HAVING filters grouped results.
WHERE vs HAVING
| WHERE | HAVING |
|---|---|
| Filters individual rows. | Filters groups. |
| Used before GROUP BY. | Used after GROUP BY. |
WHERE Salary > 40000
|
HAVING COUNT(*) > 1
|
Important GROUP BY Rule
When a column is selected without an aggregate function, that column should normally be included in the GROUP BY clause.
SELECT Department,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
Real-World Uses of GROUP BY
- Department-wise employee reports.
- City-wise customer reports.
- Product-category sales reports.
- Monthly sales summaries.
- Branch-wise transaction reports.
- Category-wise product analysis.
Important Points
- GROUP BY groups rows with the same values.
- It is commonly used with aggregate functions.
- COUNT(), SUM(), AVG(), MIN() and MAX() are common aggregate functions.
- HAVING is used to filter groups.
- GROUP BY is very useful for reporting and data analysis.
Summary
GROUP BY is used to divide rows into groups based on common values. It is mainly used with aggregate functions to generate summarized information. GROUP BY is an important SQL Server concept used extensively in reports, dashboards and data analysis.