Day 6 — Aggregate functions

Up to now every query returned rows. Today one query returns a single number — the thing management actually asks for.

The five you will use forever

COUNT() — how many
SUM() — total
AVG() — average
MIN() / MAX() — smallest and largest

COUNT

COUNT(*) vs COUNT(column) COUNT(*) counts rows. COUNT(Segment) counts rows where Segment is not NULL. Run the next example — the two numbers differ, and that difference is exactly the NULL you met on Day 4.

SUM and AVG

Averages usually need rounding before anyone sees them:

MIN and MAX

Aggregates with a WHERE

Filter first, then total. This is the shape of most MIS numbers.

The trap You cannot mix a plain column with an aggregate unless you group. SELECT SalesRep, SUM(Amount) FROM Orders has no meaning — which rep would that name belong to? That is what tomorrow is for.

Try these yourself

Count the employees.
Total salary cost of the company.
Average product price, rounded to nothing after the decimal.
The largest and smallest salary.
Total revenue of Pending orders only.

← PreviousDay 5 — SQL ORDER BY, DISTINCT and LIMIT Next →Day 7 — SQL GROUP BY explained

Want this taught properly?

This tutorial covers the basics. Our SQL course in Noida takes you through window functions, CTEs and stored procedures on a real database, with projects and mentor support.

See the SQL course →