Aggregate functions: MAX, MIN, AVG, SUM, COUNT
An aggregate (multi-row) function works on a whole column and returns one value. Table emp:
| Name | Dept | Salary |
|---|---|---|
| Asha | IT | 50000 |
| Ravi | HR | 30000 |
| Mohan | IT | 40000 |
| Zoya | Sales | 35000 |
| Kabir | HR | NULL |
| Neha | IT | 60000 |
SELECT MAX(Salary) FROM emp; -- 60000 SELECT MIN(Salary) FROM emp; -- 30000 SELECT SUM(Salary) FROM emp; -- 215000 SELECT AVG(Salary) FROM emp; -- 43000 (215000 ÷ 5, NULL skipped)
MAX and MIN also work on text and dates (alphabetical / earliest-latest).
COUNT(column) vs COUNT(*)
SELECT COUNT(Salary) FROM emp; -- 5 (NULL not counted) SELECT COUNT(*) FROM emp; -- 6 (every row) SELECT COUNT(DISTINCT Dept) FROM emp; -- 3 (IT, HR, Sales)
All aggregates ignore NULL. Only COUNT(*) counts rows, so it includes the NULL row.
GROUP BY
GROUP BY puts rows with the same value in a column into one group. The aggregate then gives one answer per group.
SELECT Dept, SUM(Salary), COUNT(*) FROM emp GROUP BY Dept;
| Dept | SUM(Salary) | COUNT(*) |
|---|---|---|
| IT | 150000 | 3 |
| HR | 30000 | 2 |
| Sales | 35000 | 1 |
Rule: every column in SELECT that is not inside an aggregate should be in GROUP BY.
HAVING vs WHERE
SELECT Dept, COUNT(*) FROM emp GROUP BY Dept HAVING COUNT(*) > 1; -- IT 3, HR 2 SELECT Dept, AVG(Salary) FROM emp WHERE Salary > 32000 GROUP BY Dept; -- IT 50000, Sales 35000
| WHERE | HAVING |
|---|---|
| filters single rows | filters groups |
| runs before GROUP BY | runs after GROUP BY |
| cannot use aggregates | usually uses aggregates |
ORDER BY
SELECT * FROM emp ORDER BY Salary; -- ASC (small → big), default SELECT * FROM emp ORDER BY Salary DESC; -- big → small SELECT * FROM emp ORDER BY Dept, Salary DESC; -- by Dept, then Salary inside each Dept SELECT Dept, SUM(Salary) FROM emp GROUP BY Dept ORDER BY SUM(Salary) DESC;
In ascending order NULL comes first in MySQL. The full order of clauses is: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …
Equi-join on two tables
A join combines rows of two tables. An equi-join uses an equals sign on a common column. Table dept: (IT, Floor 3), (HR, Floor 1), (Sales, Floor 2).
SELECT E.Name, D.Floor FROM emp E, dept D WHERE E.Dept = D.Dept; -- same with JOIN … ON SELECT emp.Name, dept.Floor FROM emp JOIN dept ON emp.Dept = dept.Dept;
E and D are short aliases for the table names. When both tables have a column with the same name, write table.column. Without the WHERE condition you get a Cartesian product: every row with every row (6 × 3 = 18 rows). The equi-join keeps only the 6 matching pairs. Both tables' Dept column appears if you use SELECT *.
Try it: your family's spending
Write 10 things your family bought this week in a table: Item, Category (Food, Travel, School), Amount. Leave one Amount blank (NULL). Predict, then run: COUNT(*) vs COUNT(Amount), SUM by Category, categories HAVING SUM(Amount) > 500, and ORDER BY SUM(Amount) DESC. Compare the piles with step 3 and step 4 of the 3D.
Key formulas and definitions
- MAX · MIN · SUM · AVG · COUNT(col) skip NULL · COUNT(*) counts all rows
- AVG = SUM ÷ COUNT(non-NULL values)
- SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … [ASC|DESC]
- Equi-join: FROM A, B WHERE A.col = B.col
- Cartesian product rows = rows(A) × rows(B)
Worked examples
1. Using emp, find the output of SELECT COUNT(*), COUNT(Salary), AVG(Salary) FROM emp;
6, 5, 43000. COUNT(*) counts Kabir's NULL row; AVG = 215000 ÷ 5.
2. Show each department with its highest salary.
SELECT Dept, MAX(Salary) FROM emp GROUP BY Dept; → IT 60000, HR 30000, Sales 35000.
3. Show departments whose average salary is more than 40000.
SELECT Dept, AVG(Salary) FROM emp GROUP BY Dept HAVING AVG(Salary) > 40000; → IT 50000. (HR average is 30000, NULL skipped.)
4. List employees from highest to lowest salary.
SELECT Name, Salary FROM emp ORDER BY Salary DESC; → Neha, Asha, Mohan, Zoya, Ravi, Kabir (NULL comes last in DESC).
5. Count employees earning more than 32000 in each department, showing only departments with at least 2 such employees.
SELECT Dept, COUNT(*) FROM emp WHERE Salary > 32000 GROUP BY Dept HAVING COUNT(*) >= 2; → IT 3.
6. Show every employee's Name with the Floor of their department.
SELECT E.Name, D.Floor FROM emp E, dept D WHERE E.Dept = D.Dept; → Asha 3, Ravi 1, Mohan 3, Zoya 2, Kabir 1, Neha 3.
7. emp has 6 rows and dept has 3 rows. How many rows does SELECT * FROM emp, dept; return?
No join condition → Cartesian product: 6 × 3 = 18 rows.
Common mistakes
- Using an aggregate in WHERE (WHERE SUM(Salary) > 1000); use HAVING after GROUP BY.
- Thinking COUNT(Salary) and COUNT(*) always match; COUNT(column) skips NULL.
- Forgetting the join condition, which gives a huge Cartesian product.
- Putting ORDER BY before GROUP BY; the clause order is WHERE → GROUP BY → HAVING → ORDER BY.