COUNT, SUM, AVG

Aggregate functions calculate on sets of rows.

Functions

• COUNT(*) � all rows
• COUNT(col) � non-NULL values
• SUM(), AVG(), MIN(), MAX()

Syntax

MYSQL

1
2
3
4
5

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;
SELECT SUM(total) AS revenue FROM orders WHERE status = "completed";
SELECT AVG(price) AS avg_price FROM products;
SELECT MIN(price), MAX(price) FROM products;

Aggregate Functions

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT
    COUNT(*) AS total_users,
    COUNT(DISTINCT city) AS unique_cities,
    AVG(age) AS avg_age,
    MIN(age) AS youngest,
    MAX(age) AS oldest,
    SUM(score) AS total_score
FROM users;

-- With filter
SELECT COUNT(*) AS adults
FROM users
WHERE age >= 18;

-- Aggregates with CASE
SELECT
    AVG(CASE WHEN gender = "M" THEN score END) AS male_avg,
    AVG(CASE WHEN gender = "F" THEN score END) AS female_avg
FROM users;

Try It Yourself

Practice

1

Exercise

Total revenue per category.

Answer

SELECT category, SUM(total) AS revenue
FROM orders o JOIN products p ON o.product_id = p.id
WHERE status = "completed"
GROUP BY category;

Quick Quiz

1

COUNT(*) vs COUNT(col)?

Same

COUNT(*) includes NULLs

COUNT(col) faster

COUNT(*) counts all rows; COUNT(col) excludes NULLs.

Retry Quiz

Interview Questions

Aggregate in WHERE?

No. Use HAVING after GROUP BY to filter aggregated results.

Next Lesson

GROUP BY & HAVING