SQL Topics
GROUP BY Clause
title: GROUP BY Clause
In real-world databases, we often need summarized information rather than individual records. Businesses rarely want to see every single transaction, order, or employee record separately. Instead, they usually need totals, averages, counts, maximum values, or minimum values grouped by a particular category.
For example:
- A company wants the total salary paid in each department.
- A school wants the average marks of students in each class.
- An online store wants the total sales for each product category.
- A bank wants the number of accounts in each branch.
Retrieving individual rows would not provide this information directly.
To solve this problem, SQL provides the GROUP BY Clause.
The GROUP BY clause organizes rows into groups based on one or more columns. Once the rows are grouped, aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN() can be applied to each group.
This makes GROUP BY one of the most important features for reporting, analytics, and business intelligence.
What is GROUP BY?
The GROUP BY clause groups rows that have the same values in specified columns.
Instead of displaying every row separately, SQL combines similar rows into groups.
Example:
SELECT Department
FROM Employees
GROUP BY Department;Result:
| Department |
|---|
| ------------ |
| HR |
| IT |
| Finance |
Even if hundreds of employees belong to the same department, each department appears only once.
Why is GROUP BY Important?
Consider an Employees table:
| EmployeeID | Name | Department |
|---|---|---|
| 1 | Rahul | IT |
| 2 | Priya | IT |
| 3 | Amit | HR |
| 4 | Neha | HR |
Without GROUP BY:
SELECT Department
FROM Employees;Output:
IT
IT
HR
HRWith GROUP BY:
SELECT Department
FROM Employees
GROUP BY Department;Output:
IT
HRGrouping removes duplication and enables meaningful analysis.
Basic GROUP BY Syntax
The general syntax is:
SELECT ColumnName,
AggregateFunction(ColumnName)
FROM TableName
GROUP BY ColumnName;Example:
SELECT Department,
COUNT(*)
FROM Employees
GROUP BY Department;This counts employees in each department.
Understanding the Syntax
Example:
SELECT Department,
COUNT(*)
FROM Employees
GROUP BY Department;SELECT
Specifies columns to display.
COUNT(*)
Counts records in each group.
FROM
Specifies the source table.
GROUP BY
Creates groups based on Department.
Result:
| Department | COUNT(*) |
|---|---|
| IT | 2 |
| HR | 2 |
GROUP BY with COUNT()
COUNT() is commonly used with GROUP BY.
Example:
SELECT Department,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department;Result:
| Department | TotalEmployees |
|---|---|
| IT | 15 |
| HR | 10 |
| Finance | 8 |
This shows the number of employees in each department.
GROUP BY with SUM()
SUM() calculates totals.
Example:
SELECT Department,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;Result:
| Department | TotalSalary |
|---|---|
| IT | 800000 |
| HR | 350000 |
This shows total salary expenditure per department.
GROUP BY with AVG()
AVG() calculates averages.
Example:
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;Result:
| Department | AverageSalary |
|---|---|
| IT | 53333 |
| HR | 35000 |
Useful for performance and salary analysis.
GROUP BY with MAX()
MAX() finds the highest value in each group.
Example:
SELECT Department,
MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY Department;Result:
| Department | HighestSalary |
|---|---|
| IT | 90000 |
| HR | 60000 |
GROUP BY with MIN()
MIN() finds the smallest value in each group.
Example:
SELECT Department,
MIN(Salary) AS LowestSalary
FROM Employees
GROUP BY Department;Result:
| Department | LowestSalary |
|---|---|
| IT | 25000 |
| HR | 18000 |
Creating a Sample Table
Example:
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);Insert data:
INSERT INTO Employees
VALUES
(1, 'Rahul', 'IT', 50000),
(2, 'Priya', 'IT', 60000),
(3, 'Amit', 'HR', 30000),
(4, 'Neha', 'HR', 40000);Grouping Employee Salaries
Query:
SELECT Department,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;Result:
| Department | TotalSalary |
|---|---|
| IT | 110000 |
| HR | 70000 |
This summarizes salary expenses by department.
GROUP BY Multiple Columns
SQL can group by more than one column.
Example:
SELECT Department,
City,
COUNT(*)
FROM Employees
GROUP BY Department, City;Grouping process:
- Group by Department.
- Within each department, group by City.
Result:
| Department | City | COUNT(*) |
|---|---|---|
| IT | Delhi | 5 |
| IT | Mumbai | 3 |
| HR | Delhi | 2 |
GROUP BY with ORDER BY
Grouping and sorting are commonly used together.
Example:
SELECT Department,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
ORDER BY TotalEmployees DESC;Result:
Departments are sorted by employee count.
Real-World Example
Imagine an e-commerce company.
Orders table:
| OrderID | Category | Amount |
|---|---|---|
| 1 | Electronics | 20000 |
| 2 | Electronics | 30000 |
| 3 | Clothing | 5000 |
Query:
SELECT Category,
SUM(Amount) AS TotalSales
FROM Orders
GROUP BY Category;Result:
| Category | TotalSales |
|---|---|
| Electronics | 50000 |
| Clothing | 5000 |
This helps management analyze sales performance.
Common Errors
Missing GROUP BY Column
Wrong:
SELECT Department,
Salary
FROM Employees
GROUP BY Department;Error:
Salary must be aggregated or grouped.Correct:
SELECT Department,
SUM(Salary)
FROM Employees
GROUP BY Department;Using Aggregate Functions Incorrectly
Wrong:
SELECT SUM(Salary)
FROM Employees
GROUP BY SUM(Salary);Aggregate functions should not be used inside GROUP BY.
Forgetting GROUP BY
Wrong:
SELECT Department,
COUNT(*)
FROM Employees;This may produce an error depending on the database system.
Best Practices
Group Meaningful Data
Group data based on business requirements.
Examples:
- Department
- City
- Category
- Year
Use Aliases
Example:
COUNT(*) AS TotalEmployeesImproves readability.
Combine with ORDER BY
Sort summarized data for better analysis.
Avoid Excessive Grouping
Too many grouping columns can create unnecessary complexity.
Use Aggregate Functions Properly
Always aggregate columns that are not included in GROUP BY.
Common Interview Questions
What is GROUP BY used for?
GROUP BY groups rows that share the same values and allows aggregate calculations on those groups.
Which functions are commonly used with GROUP BY?
- COUNT()
- SUM()
- AVG()
- MAX()
- MIN()
Can GROUP BY use multiple columns?
Yes.
Example:
GROUP BY Department, City;What happens before aggregation?
Rows are grouped according to the GROUP BY columns.
Summary
The GROUP BY clause is a powerful SQL feature used to organize rows into groups and perform aggregate calculations on those groups. It plays a critical role in reporting, analytics, and business intelligence systems.
In this lesson, you learned:
- What GROUP BY is
- Why grouping is important
- GROUP BY syntax
- COUNT(), SUM(), AVG(), MAX(), and MIN()
- Multiple-column grouping
- GROUP BY with ORDER BY
- Common mistakes
- Best practices
Mastering GROUP BY is essential because most business reports and analytical queries depend on grouping and summarizing data effectively.
Next Step
Continue to the next lesson:
HAVING Clause →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for GROUP BY Clause.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, filtering, group
Related SQL Topics