Aggregate Functions in SQL
Concepts, Syntax & Practical Examples
Database Systems
Database Systems Aggregate Functions in SQL 1 / 29
Learning Objectives
Understand aggregate functions
Use COUNT, SUM, AVG, MIN, MAX
Apply GROUP BY correctly
Use HAVING for filtering
Differentiate WHERE and HAVING
Database Systems Aggregate Functions in SQL 2 / 29
Example Table – Employee
Employee(emp id, name, department, salary, age)
emp id name department salary age
1 Ali IT 50000 25
2 Sara HR 40000 30
3 Ahmed IT 60000 28
4 Ayesha HR 45000 26
5 Bilal IT 55000 32
Database Systems Aggregate Functions in SQL 3 / 29
COUNT() Example
SELECT COUNT (*) FROM Employee ;
SELECT COUNT (*)
FROM Employee
WHERE department = ’ IT ’;
Database Systems Aggregate Functions in SQL 4 / 29
SUM() Example
SELECT SUM ( salary ) AS total_salary
FROM Employee ;
Database Systems Aggregate Functions in SQL 5 / 29
AVG() Example
SELECT AVG ( salary ) AS average_salary
FROM Employee ;
Database Systems Aggregate Functions in SQL 6 / 29
MIN() and MAX()
SELECT MIN ( salary ) FROM Employee ;
SELECT MAX ( salary ) FROM Employee ;
Database Systems Aggregate Functions in SQL 7 / 29
GROUP BY Example
SELECT department , SUM ( salary )
FROM Employee
GROUP BY department ;
Database Systems Aggregate Functions in SQL 8 / 29
HAVING Example
SELECT department , SUM ( salary )
FROM Employee
GROUP BY department
HAVING SUM ( salary ) > 100000;
Database Systems Aggregate Functions in SQL 9 / 29
WHERE vs HAVING
WHERE HAVING
Filters rows Filters groups
Before GROUP BY After GROUP BY
Cannot use aggregates Can use aggregates
Database Systems Aggregate Functions in SQL 10 / 29
Additional Sample Data
New Records Added:
emp id name department salary age
6 Zain Sales 48000 29
7 Hina Sales 52000 27
Database Systems Aggregate Functions in SQL 11 / 29
1. Department-wise Employee Count
SELECT department , COUNT (*) AS total_employees
FROM Employee
GROUP BY department ;
Database Systems Aggregate Functions in SQL 12 / 29
2. Department-wise Average Salary
SELECT department , AVG ( salary ) AS avg_salary
FROM Employee
GROUP BY department ;
Database Systems Aggregate Functions in SQL 13 / 29
3. Max and Min Salary per Department
SELECT department ,
MAX ( salary ) AS highest_salary ,
MIN ( salary ) AS lowest_salary
FROM Employee
GROUP BY department ;
Database Systems Aggregate Functions in SQL 14 / 29
4. Departments Having More Than 2 Employees
SELECT department , COUNT (*) AS total
FROM Employee
GROUP BY department
HAVING COUNT (*) > 2;
Database Systems Aggregate Functions in SQL 15 / 29
5. Departments Where Average Salary ¿ 50000
SELECT department , AVG ( salary ) AS avg_salary
FROM Employee
GROUP BY department
HAVING AVG ( salary ) > 50000;
Database Systems Aggregate Functions in SQL 16 / 29
6. Total Salary Paid Per Department
SELECT department , SUM ( salary ) AS total_salary
FROM Employee
GROUP BY department ;
Database Systems Aggregate Functions in SQL 17 / 29
7. Age Statistics Per Department
SELECT department ,
AVG ( age ) AS avg_age ,
MAX ( age ) AS oldest_employee ,
MIN ( age ) AS y ounges t_empl oyee
FROM Employee
GROUP BY department ;
Database Systems Aggregate Functions in SQL 18 / 29
8. Total Salary Between 90000 and 200000
SELECT department , SUM ( salary ) AS total_salary
FROM Employee
GROUP BY department
HAVING SUM ( salary ) BETWEEN 90000 AND 200000;
Database Systems Aggregate Functions in SQL 19 / 29
9. High Earners Per Department
SELECT department , COUNT (*) AS high_earners
FROM Employee
WHERE salary > 50000
GROUP BY department ;
Database Systems Aggregate Functions in SQL 20 / 29
10. Salary Difference (Max - Min)
SELECT department ,
MAX ( salary ) - MIN ( salary ) AS s alary_ differ ence
FROM Employee
GROUP BY department ;
Database Systems Aggregate Functions in SQL 21 / 29
11. Young Average Age but High Salary
SELECT department ,
AVG ( age ) AS avg_age ,
AVG ( salary ) AS avg_salary
FROM Employee
GROUP BY department
HAVING AVG ( age ) < 30 AND AVG ( salary ) > 45000;
Database Systems Aggregate Functions in SQL 22 / 29
12. Count Distinct Ages Per Department
SELECT department , COUNT ( DISTINCT age ) AS unique_ages
FROM Employee
GROUP BY department ;
Database Systems Aggregate Functions in SQL 23 / 29
13. Salary Ranking Insight
SELECT department ,
SUM ( salary ) AS total_salary ,
AVG ( salary ) AS avg_salary ,
COUNT (*) AS total_employees
FROM Employee
GROUP BY department
ORDER BY total_salary DESC ;
Database Systems Aggregate Functions in SQL 24 / 29
14. Departments With Exactly 2 Employees
SELECT department , COUNT (*) AS total
FROM Employee
GROUP BY department
HAVING COUNT (*) = 2;
Database Systems Aggregate Functions in SQL 25 / 29
15. Departments Where Max Salary ¿ 55000
SELECT department , MAX ( salary ) AS highest_salary
FROM Employee
GROUP BY department
HAVING MAX ( salary ) > 55000;
Database Systems Aggregate Functions in SQL 26 / 29
SQL Execution Order Reminder
1 FROM
2 WHERE
3 GROUP BY
4 HAVING
5 SELECT
6 ORDER BY
Database Systems Aggregate Functions in SQL 27 / 29
Important GROUP BY Rule
Every column in SELECT must be:
Inside an aggregate function
OR included in GROUP BY
Database Systems Aggregate Functions in SQL 28 / 29
Thank You
Database Systems Aggregate Functions in SQL 29 / 29