Aggregate Functions & grouping

What are Aggregate Functions? #

Aggregate functions:
Take multiple rows and return one summarized value

Main Aggregate Functions #

SUM() – Total #

Adds values from a column

SELECT SUM(salary)
FROM employees;

Returns total salary of all employees

With Condition #

SELECT SUM(salary)
FROM employees
WHERE department = 'IT';

Total salary of IT department only

AVG() – Average #

Calculates average value

SELECT AVG(salary)
FROM employees;

Average salary

With Filter #

SELECT AVG(salary)
FROM employees
WHERE department = 'HR';

Average salary in HR

COUNT() – Count Rows #

Counts number of records

SELECT COUNT(*)
FROM employees;

Total number of employees

Count Specific Column #

SELECT COUNT(salary)
FROM employees;

✔ Counts only non-null salary values

Count with Condition #

SELECT COUNT(*)
FROM employees
WHERE salary > 50000;

Employees earning more than 50k

MIN() – Smallest Value #

SELECT MIN(salary)
FROM employees;

Lowest salary

MAX() – Largest Value #

SELECT MAX(salary)
FROM employees;

Highest salary

GROUP BY Clause #

Here’s the key idea:
Aggregate functions give one result
GROUP BY gives multiple results (per category)

Syntax #

SELECT column, AGG_FUNCTION(column)
FROM table
GROUP BY column;

Output:

• Sales → 10
• HR → 5
• IT → 8

Example: Average Salary per Department #

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Each department gets its own average

Important Rule #

Every column in SELECT must be:

• Either inside an aggregate function
• Or included in GROUP BY

Wrong:

SELECT department, salary
FROM employees
GROUP BY department;

Error (salary is not aggregated)

GROUP BY with WHERE #

Filter rows before grouping

SELECT department, SUM(salary)
FROM employees
WHERE salary > 30000
GROUP BY department;

Only includes employees with salary > 30k

HAVING Clause #

Now the important distinction:

WHERE filters rows
HAVING filters groups

Syntax #

SELECT column, AGG_FUNCTION(column)
FROM table
GROUP BY column
HAVING condition;

Example: Departments with More Than 5 Employees

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Only shows departments with > 5 employees

Example: High Salary Departments #

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

Only departments where average salary > 60k

WHERE vs HAVING (Critical Difference) #

Example Comparison #

-- WHERE (row filtering)
SELECT department, AVG(salary)
FROM employees
WHERE salary > 30000
GROUP BY department;

-- HAVING (group filtering)
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 30000;

Real-World Combined Example #

Requirement:

• Only Sales & HR
• Salary > 40k
• Show avg salary per department
• Only include departments with avg > 60k

SELECT department, AVG(salary)
FROM employees
WHERE department IN ('Sales', 'HR')
AND salary > 40000
GROUP BY department
HAVING AVG(salary) > 60000;