What is GROUP BY?
Sample Students Table
| ID | Name | Grade |
|---|---|---|
| 1 | John | A |
| 2 | Mary | B |
| 3 | Peter | A |
| 4 | Sarah | B |
| 5 | Tom | A |
5 rows
Original Employees Table
| Name | Department | Salary |
|---|---|---|
| Alice | Sales | 60000 |
| Bob | IT | 80000 |
| Carol | Sales | 70000 |
| Dave | IT | 90000 |
4 rows
WHERE vs HAVING
| Aspect | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups |
| Used with | Any query | GROUP BY queries |
| Timing | Before grouping | After grouping |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
GROUP BY groups rows that have the same values into summary rows. It's like sorting items into categories and counting each category.
Real-life example: Imagine you have a list of students. GROUP BY helps you count how many students are in each grade (A, B, C).
Simple Example
Sample Students Table
| ID | Name | Grade |
|---|---|---|
| 1 | John | A |
| 2 | Mary | B |
| 3 | Peter | A |
| 4 | Sarah | B |
| 5 | Tom | A |
5 rows
Original Employees Table
| Name | Department | Salary |
|---|---|---|
| Alice | Sales | 60000 |
| Bob | IT | 80000 |
| Carol | Sales | 70000 |
| Dave | IT | 90000 |
4 rows
WHERE vs HAVING
| Aspect | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups |
| Used with | Any query | GROUP BY queries |
| Timing | Before grouping | After grouping |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
Without GROUP BY (shows all students):
SELECT grade FROM students;
-- Result: A, B, A, B, AWith GROUP BY (counts each grade):
SELECT grade, COUNT(*) AS total
FROM students
GROUP BY grade;
-- Result:
-- A: 3 students
-- B: 2 studentsBasic Syntax
Sample Students Table
| ID | Name | Grade |
|---|---|---|
| 1 | John | A |
| 2 | Mary | B |
| 3 | Peter | A |
| 4 | Sarah | B |
| 5 | Tom | A |
5 rows
Original Employees Table
| Name | Department | Salary |
|---|---|---|
| Alice | Sales | 60000 |
| Bob | IT | 80000 |
| Carol | Sales | 70000 |
| Dave | IT | 90000 |
4 rows
WHERE vs HAVING
| Aspect | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups |
| Used with | Any query | GROUP BY queries |
| Timing | Before grouping | After grouping |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
SELECT column, COUNT(*)
FROM table
GROUP BY column;Easy Examples
Sample Students Table
| ID | Name | Grade |
|---|---|---|
| 1 | John | A |
| 2 | Mary | B |
| 3 | Peter | A |
| 4 | Sarah | B |
| 5 | Tom | A |
5 rows
Original Employees Table
| Name | Department | Salary |
|---|---|---|
| Alice | Sales | 60000 |
| Bob | IT | 80000 |
| Carol | Sales | 70000 |
| Dave | IT | 90000 |
4 rows
WHERE vs HAVING
| Aspect | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups |
| Used with | Any query | GROUP BY queries |
| Timing | Before grouping | After grouping |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
Count by Category
SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade;Sum by Group
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;Average by Group
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;Important Rule
Sample Students Table
| ID | Name | Grade |
|---|---|---|
| 1 | John | A |
| 2 | Mary | B |
| 3 | Peter | A |
| 4 | Sarah | B |
| 5 | Tom | A |
5 rows
Original Employees Table
| Name | Department | Salary |
|---|---|---|
| Alice | Sales | 60000 |
| Bob | IT | 80000 |
| Carol | Sales | 70000 |
| Dave | IT | 90000 |
4 rows
WHERE vs HAVING
| Aspect | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups |
| Used with | Any query | GROUP BY queries |
| Timing | Before grouping | After grouping |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
Every column in SELECT must be either:
- In the GROUP BY clause, OR
- Inside an aggregate function (COUNT, SUM, AVG, etc.)
Wrong ❌:
SELECT name, department, COUNT(*)
FROM employees
GROUP BY department;
-- Error! name is not groupedCorrect ✅:
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;Summary
Sample Students Table
| ID | Name | Grade |
|---|---|---|
| 1 | John | A |
| 2 | Mary | B |
| 3 | Peter | A |
| 4 | Sarah | B |
| 5 | Tom | A |
5 rows
Original Employees Table
| Name | Department | Salary |
|---|---|---|
| Alice | Sales | 60000 |
| Bob | IT | 80000 |
| Carol | Sales | 70000 |
| Dave | IT | 90000 |
4 rows
WHERE vs HAVING
| Aspect | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups |
| Used with | Any query | GROUP BY queries |
| Timing | Before grouping | After grouping |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
- GROUP BY organizes data into groups
- Use with COUNT(), SUM(), AVG() to calculate for each group
- Every SELECT column must be grouped or aggregated
- Perfect for reports and summaries
SQL Editor
Loading...
Count students in each grade
Output
Click "Run Query" to see results
SQL Editor
Loading...
Average age per grade, sorted
Output
Click "Run Query" to see results
SQL Editor
Loading...
Grades with 2 or more students
Output
Click "Run Query" to see results
SQL Editor
Loading...
Complete GROUP BY with all clauses
Output
Click "Run Query" to see results