Aggregate Functions and GROUP BY

Most examples use this employees table:

id	name	dept	salary	city
1	Asha	IT	65000	Pune
2	Ravi	HR	42000	Mumbai
3	Meera	IT	78000	Pune
4	Karan	Sales	51000	Delhi
5	Neha	HR	47000	Pune

Function	Returns
COUNT()	Number of rows
SUM()	Total
AVG()	Average
MIN() / MAX()	Smallest / largest value

SQL

SELECT dept, COUNT(*) AS staff, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept;

+-------+-------+------------+ | dept | staff | avg_salary | +-------+-------+------------+ | IT | 2 | 71500.00 | | HR | 2 | 44500.00 | | Sales | 1 | 51000.00 | +-------+-------+------------+

HAVING

WHERE filters rows before grouping; HAVING filters groups after grouping.

SQL

SELECT city, SUM(salary) AS total
FROM employees
GROUP BY city
HAVING SUM(salary) > 100000;

+------+--------+ | city | total | +------+--------+ | Pune | 190000 | +------+--------+

Logical Order of Execution

Text

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Track your progress through the SQL course

← Prev Next →