SQL GROUP BY Syntax
SELECT column, AGGREGATE(column)
FROM table_name
WHERE condition
GROUP BY column;Every column in SELECT that is not inside an aggregate function must appear in the GROUP BY clause.
Sample Table
+----+-----------+------------+--------+-----------+
| ID | Name | Department | Salary | City |
+----+-----------+------------+--------+-----------+
| 1 | Priya K. | Sales | 55000 | Mumbai |
| 2 | John D. | Marketing | 62000 | London |
| 3 | Arun M. | IT | 78000 | Delhi |
| 4 | Emma W. | Sales | 51000 | Sydney |
| 5 | Ravi S. | IT | 72000 | Mumbai |
| 6 | Sara L. | HR | 58000 | New York |
+----+-----------+------------+--------+-----------+GROUP BY with COUNT
Count the number of employees in each department:
SELECT Department, COUNT(*) AS EmpCount
FROM Employees
GROUP BY Department;+------------+----------+ | Department | EmpCount | +------------+----------+ | HR | 1 | | IT | 2 | | Marketing | 1 | | Sales | 2 | +------------+----------+
GROUP BY with SUM and AVG
SELECT Department,
SUM(Salary) AS TotalSalary,
ROUND(AVG(Salary), 0) AS AvgSalary
FROM Employees
GROUP BY Department;+------------+-------------+-----------+ | Department | TotalSalary | AvgSalary | +------------+-------------+-----------+ | HR | 58000 | 58000 | | IT | 150000 | 75000 | | Marketing | 62000 | 62000 | | Sales | 106000 | 53000 | +------------+-------------+-----------+
GROUP BY with WHERE
The WHERE clause filters rows before grouping. This query counts employees per department, but only those earning above 55000:
SELECT Department, COUNT(*) AS EmpCount
FROM Employees
WHERE Salary > 55000
GROUP BY Department;+------------+----------+ | Department | EmpCount | +------------+----------+ | HR | 1 | | IT | 2 | | Marketing | 1 | +------------+----------+
Sales is excluded because neither Sales employee earns above 55000.
GROUP BY Multiple Columns
You can group by more than one column to create finer groups:
SELECT Department, City, COUNT(*) AS EmpCount
FROM Employees
GROUP BY Department, City;+------------+-----------+----------+ | Department | City | EmpCount | +------------+-----------+----------+ | HR | New York | 1 | | IT | Delhi | 1 | | IT | Mumbai | 1 | | Marketing | London | 1 | | Sales | Mumbai | 1 | | Sales | Sydney | 1 | +------------+-----------+----------+
GROUP BY with ORDER BY
Sort grouped results using ORDER BY:
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
ORDER BY TotalSalary DESC;+------------+-------------+ | Department | TotalSalary | +------------+-------------+ | IT | 150000 | | Sales | 106000 | | Marketing | 62000 | | HR | 58000 | +------------+-------------+
⚠️ Common GROUP BY Error
If you include a column in SELECT that is not in GROUP BY and not inside an aggregate function, most databases will throw an error. For example: SELECT Department, Name FROM Employees GROUP BY Department fails because Name is not grouped or aggregated.
For the complete GROUP BY reference, see the official MySQL GROUP BY documentation. In the next chapter, you will learn SQL HAVING to filter groups after aggregation.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a query that shows each City with the number of employees and the highest salary in that city, sorted by highest salary descending.
SELECT City,
COUNT(*) AS EmpCount,
MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY City
ORDER BY HighestSalary DESC;Expected Output:
+-----------+----------+---------------+ | City | EmpCount | HighestSalary | +-----------+----------+---------------+ | Delhi | 1 | 78000 | | Mumbai | 2 | 72000 | | London | 1 | 62000 | | New York | 1 | 58000 | | Sydney | 1 | 51000 | +-----------+----------+---------------+
📝 What You Learned in This Chapter
GROUP BYgroups rows with the same column values together- Use aggregate functions (COUNT, SUM, AVG, MIN, MAX) with GROUP BY
- Every non-aggregated column in SELECT must be in the GROUP BY clause
- WHERE filters rows before grouping
- You can group by multiple columns for finer groups
- Combine GROUP BY with ORDER BY to sort the grouped results
Frequently Asked Questions
GROUP BY Department, City creates a separate group for each unique combination of Department and City. This gives you finer-grained groups than grouping by a single column.WHERE filters individual rows before grouping. HAVING filters groups after aggregation. For example, WHERE can filter by Salary > 50000 before grouping, while HAVING can filter by COUNT(*) > 2 after grouping.SELECT Department, Name GROUP BY Department fails because Name is not grouped.SELECT Department FROM Employees GROUP BY Department works and returns unique departments (similar to DISTINCT). However, GROUP BY is designed for use with aggregate functions. Use SELECT DISTINCT if you just need unique values.📖 Chapter 25 of 30 — SQL Tutorial on Neotech Navigators
