🗄️ Unit 3 – Database & SQL · Lesson 22
Aggregate Functions, GROUP BY, HAVING
Sample table
All queries in this lesson use the table EMPLOYEE.
SQL
SELECT * FROM EMPLOYEE;
Output
+-------+--------+-------+--------+--------+-------+--------+ | EmpNo | Name | Dept | Gender | Salary | Bonus | City | +-------+--------+-------+--------+--------+-------+--------+ | 101 | Aman | Sales | M | 45000 | 5000 | Delhi | | 102 | Bina | IT | F | 72000 | NULL | Pune | | 103 | Chirag | Sales | M | 38000 | 3000 | Mumbai | | 104 | Divya | HR | F | 51000 | 4000 | Delhi | | 105 | Eshan | IT | M | 86000 | 9000 | Pune | | 106 | Fatima | Sales | F | 41000 | NULL | Delhi | | 107 | Gopal | HR | M | 47000 | 2500 | Mumbai | | 108 | Hema | IT | F | 64000 | 6000 | Delhi | +-------+--------+-------+--------+--------+-------+--------+
Aggregate functions
Aggregate (multi-row) functions work on a set of rows and return a single value.
⬆️
MAX(col)Largest value⬇️
MIN(col)Smallest value➕
SUM(col)Total of values➗
AVG(col)Average of values🔢
COUNT(*) / COUNT(col)Number of rows / non-NULL valuesSQL
SELECT MAX(Salary), MIN(Salary), SUM(Salary), AVG(Salary) FROM EMPLOYEE;
Output
+-------------+-------------+-------------+-------------+ | MAX(Salary) | MIN(Salary) | SUM(Salary) | AVG(Salary) | +-------------+-------------+-------------+-------------+ | 86000 | 38000 | 444000 | 55500.0000 | +-------------+-------------+-------------+-------------+
COUNT and NULL
Two employees have no bonus (NULL). Aggregates skip NULLs, so AVG(Bonus) divides by 6, not 8.
SQL
SELECT COUNT(*), COUNT(Bonus), COUNT(DISTINCT Dept) FROM EMPLOYEE;
Output
+----------+--------------+----------------------+ | COUNT(*) | COUNT(Bonus) | COUNT(DISTINCT Dept) | +----------+--------------+----------------------+ | 8 | 6 | 3 | +----------+--------------+----------------------+
SQL
SELECT AVG(Bonus), SUM(Bonus) / COUNT(*) AS 'Avg counting NULL as 0' FROM EMPLOYEE;
Output
+------------+------------------------+ | AVG(Bonus) | Avg counting NULL as 0 | +------------+------------------------+ | 4916.6667 | 3687.5000 | +------------+------------------------+
Aggregates with WHERE
SQL
SELECT MAX(Salary) AS Highest FROM EMPLOYEE WHERE City = 'Delhi';
Output
+---------+ | Highest | +---------+ | 64000 | +---------+
GROUP BY
GROUP BY puts rows with the same value in a column into one group and applies the aggregate function to each group separately.
| Dept | Name | Salary |
|---|---|---|
| HR | Divya | 51000 |
| HR | Gopal | 47000 |
| IT | Bina | 72000 |
| IT | Eshan | 86000 |
| IT | Hema | 64000 |
| Sales | Aman | 45000 |
| Sales | Chirag | 38000 |
| Sales | Fatima | 41000 |
SQL
SELECT Dept, COUNT(*) AS Staff, SUM(Salary) AS Total FROM EMPLOYEE GROUP BY Dept ORDER BY Dept;
Output
+-------+-------+--------+ | Dept | Staff | Total | +-------+-------+--------+ | HR | 2 | 98000 | | IT | 3 | 222000 | | Sales | 3 | 124000 | +-------+-------+--------+
Grouping on two columns
SQL
SELECT Dept, Gender, COUNT(*) FROM EMPLOYEE GROUP BY Dept, Gender ORDER BY Dept, Gender;
Output
+-------+--------+----------+ | Dept | Gender | COUNT(*) | +-------+--------+----------+ | HR | F | 1 | | HR | M | 1 | | IT | F | 2 | | IT | M | 1 | | Sales | F | 1 | | Sales | M | 2 | +-------+--------+----------+
SQL
SELECT City, ROUND(AVG(Salary)) AS AvgSal FROM EMPLOYEE GROUP BY City ORDER BY AvgSal DESC;
Output
+--------+--------+ | City | AvgSal | +--------+--------+ | Pune | 79000 | | Delhi | 50250 | | Mumbai | 42500 | +--------+--------+
HAVING
HAVING puts a condition on groups – just as WHERE puts a condition on rows.
SQL
SELECT Dept, AVG(Salary) FROM EMPLOYEE GROUP BY Dept HAVING AVG(Salary) > 50000 ORDER BY Dept;
Output
+------+-------------+ | Dept | AVG(Salary) | +------+-------------+ | IT | 74000.0000 | +------+-------------+
SQL
SELECT City, COUNT(*) FROM EMPLOYEE WHERE Gender = 'F' GROUP BY City HAVING COUNT(*) >= 2;
Output
+-------+----------+ | City | COUNT(*) | +-------+----------+ | Delhi | 3 | +-------+----------+
WHERE vs HAVING
WHERE
- Filters rows
- Before grouping
- Cannot use SUM, AVG…
- WHERE Gender = 'F'
HAVING
- Filters groups
- After GROUP BY
- Can use aggregate functions
- HAVING COUNT(*) >= 2
ORDER BY with groups
SQL
SELECT Dept, MAX(Salary) - MIN(Salary) AS Gap FROM EMPLOYEE GROUP BY Dept ORDER BY Gap DESC;
Output
+-------+-------+ | Dept | Gap | +-------+-------+ | IT | 22000 | | Sales | 7000 | | HR | 4000 | +-------+-------+
SQL
SELECT Dept, Name, Salary FROM EMPLOYEE ORDER BY Dept, Salary DESC;
Output
+-------+--------+--------+ | Dept | Name | Salary | +-------+--------+--------+ | HR | Divya | 51000 | | HR | Gopal | 47000 | | IT | Eshan | 86000 | | IT | Bina | 72000 | | IT | Hema | 64000 | | Sales | Aman | 45000 | | Sales | Fatima | 41000 | | Sales | Chirag | 38000 | +-------+--------+--------+
Practice
- Show the number of employees in each city. SELECT City, COUNT(*) FROM EMPLOYEE GROUP BY City;
- Show departments whose total salary exceeds 1,00,000. … GROUP BY Dept HAVING SUM(Salary) > 100000;
- Predict: SELECT COUNT(Bonus) FROM EMPLOYEE WHERE Dept = 'Sales'; Answer: 2
Frequently Asked Questions
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts all rows. COUNT(column) counts only rows where that column is not NULL.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use them.
Do aggregate functions include NULL values?
No. SUM, AVG, MAX, MIN and COUNT(column) ignore NULLs; only COUNT(*) counts every row.
