SQL · Theory

Aggregate Functions

Theory

117/439

Aggregate Functions

Aggregates: COUNT, SUM, AVG, MIN, MAX. They take many rows and return one value per group (or the whole table if no GROUP BY). SELECT AVG(marks) FROM students; one number. SELECT city, AVG(marks) FROM students GROUP BY city; one number per city.

COUNT(*) counts rows. COUNT(marks) skips NULL. SUM of empty set is NULL not 0 — COALESCE if you need 0.

Trap — AVG of a VARCHAR that looks like numbers. Trap: extra columns in SELECT not grouped.

On the example next to this theory — Aggregate functions collapse many rows into one summary row.

Exam tip

AVG(marks) whole class, or GROUP BY city.

Aggregate Functions — sample query

CREATE TABLE scores (id INTEGER PRIMARY KEY, marks INTEGER);
INSERT INTO scores VALUES (1, 80), (2, 90), (3, 70);
SELECT COUNT(*) AS n, AVG(marks) AS avg_marks, MAX(marks) AS top
FROM scores;

Aggregate functions collapse many rows into one summary row.

Short notes

DefCOUNT SUM AVG MIN MAX summarise many rows.
RuleGROUP BY for per-group. COUNT(*) vs COUNT(col).
TrapSELECT name, AVG(marks) without GROUP BY name.

SQL · Theory

Aggregate Functions

← All stacks

Theory

117/439

Aggregate Functions

COUNT(*) counts rows. COUNT(marks) skips NULL. SUM of empty set is NULL not 0 — COALESCE if you need 0.

Trap — AVG of a VARCHAR that looks like numbers. Trap: extra columns in SELECT not grouped.

On the example next to this theory — Aggregate functions collapse many rows into one summary row.

Exam tip

AVG(marks) whole class, or GROUP BY city.

Aggregate Functions — sample query

CREATE TABLE scores (id INTEGER PRIMARY KEY, marks INTEGER);
INSERT INTO scores VALUES (1, 80), (2, 90), (3, 70);
SELECT COUNT(*) AS n, AVG(marks) AS avg_marks, MAX(marks) AS top
FROM scores;

Aggregate functions collapse many rows into one summary row.

Short notes

DefCOUNT SUM AVG MIN MAX summarise many rows.
RuleGROUP BY for per-group. COUNT(*) vs COUNT(col).
TrapSELECT name, AVG(marks) without GROUP BY name.

Aggregate Functions

Aggregate Functions — sample query

Short notes

Questions

Aggregate Functions

Aggregate Functions — sample query

Short notes

Questions