SQL — Null Functions

What are aggregate functions?

Aggregate functions calculate a single value from multiple rows:

SELECT COUNT(*) FROM students;          -- count all rows
SELECT SUM(salary) FROM employees;      -- total
SELECT AVG(age) FROM students;          -- average
SELECT MIN(age), MAX(age) FROM students; -- extremes

COUNT — counting rows

-- Count all rows
SELECT COUNT(*) FROM students;

-- Count non-NULL values in a column
SELECT COUNT(email) FROM students;

-- Count unique values
SELECT COUNT(DISTINCT grade) FROM students;

-- Count with condition
SELECT COUNT(*) FROM students WHERE age > 20;

COUNT(*) counts all rows including NULLs. COUNT(column) skips NULLs.

SUM — totaling values

-- Total salary
SELECT SUM(salary) FROM employees;

-- Total per department
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

SUM ignores NULL values — rows with NULL don't affect the total.

AVG — average

-- Overall average
SELECT AVG(age) FROM students;

-- Average per grade
SELECT grade, ROUND(AVG(age), 1) AS avg_age
FROM students
GROUP BY grade;

AVG also ignores NULLs. To include NULLs as zeros, use AVG(COALESCE(age, 0)).

MIN and MAX — extremes

-- Smallest and largest age
SELECT MIN(age), MAX(age) FROM students;

-- Oldest student per grade
SELECT grade, MAX(age) AS oldest
FROM students
GROUP BY grade;

-- Most recent order date
SELECT MAX(order_date) FROM orders;

GROUP BY — grouping rows

-- Count students per grade
SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade;

Output:

+-------+---------------+
| grade | student_count |
+-------+---------------+
| A     | 12            |
| B     | 8             |
| C     | 5             |
| D     | 3             |
| F     | 2             |
+-------+---------------+

Each unique grade becomes one row. The aggregate function runs within each group.

Multiple groupings

-- Count students per grade AND age group
SELECT grade, age, COUNT(*)
FROM students
GROUP BY grade, age
ORDER BY grade, age;

Groups by the combination of grade and age. Use when you need sub-categories.

HAVING — filtering groups

-- Grades with more than 5 students
SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade
HAVING COUNT(*) > 5;

-- Departments with total salary over 500000
SELECT department, SUM(salary) AS total
FROM employees
GROUP BY department
HAVING SUM(salary) > 500000;

HAVING filters groups AFTER aggregation. WHERE filters rows BEFORE grouping.

WHERE vs HAVING

-- WHERE filters rows first, then groups
SELECT grade, COUNT(*)
FROM students
WHERE age >= 18          -- filters rows first
GROUP BY grade
HAVING COUNT(*) > 5;    -- then filters groups

Clause	Filters	When
WHERE	Individual rows	Before GROUP BY
HAVING	Groups	After GROUP BY

GROUP BY with multiple columns

SELECT department, role, COUNT(*), AVG(salary)
FROM employees
GROUP BY department, role
ORDER BY department, COUNT(*) DESC;

Each unique combination of department and role becomes one group.

Aggregate with JOIN

SELECT s.name, COUNT(e.course_id) AS courses_enrolled
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.name
HAVING COUNT(e.course_id) >= 3;

Rollup and Cube

-- ROLLUP adds subtotals
SELECT department, role, SUM(salary)
FROM employees
GROUP BY ROLLUP(department, role);

-- CUBE adds all combinations of subtotals
SELECT department, role, SUM(salary)
FROM employees
GROUP BY CUBE(department, role);

ROLLUP adds summary rows for hierarchical groupings. CUBE adds all possible subtotals.

Common patterns

Top N per group

-- Top 3 students per grade
SELECT * FROM (
    SELECT name, grade, score,
           ROW_NUMBER() OVER (PARTITION BY grade ORDER BY score DESC) AS rank
    FROM students
) ranked
WHERE rank <= 3;

Conditional aggregation

SELECT
    grade,
    COUNT(*) AS total,
    SUM(CASE WHEN age > 20 THEN 1 ELSE 0 END) AS over_20,
    SUM(CASE WHEN age <= 20 THEN 1 ELSE 0 END) AS under_20
FROM students
GROUP BY grade;

Percentage calculation

SELECT
    grade,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM students), 1) AS percentage
FROM students
GROUP BY grade
ORDER BY count DESC;

Mini Practice

1. Count the number of students in each grade
2. Calculate the average age per grade and only show grades with average above 20
3. Find the department with the highest total salary
4. Use GROUP BY with JOIN to count courses per student
5. Write a query with ROLLUP to show subtotals

Next: creating and modifying tables →