SQL · Theory

Aggregate Functions

← All stacks

Theory

117/439

Aggregate Functions

Aggregate Functions is simple if you keep it on paper. Priya uses it for average per subject in a report.

Aggregate Functions on the board — GROUP BY subject. Then say what average per subject looks like after.

Aggregate Functions exists so Priya can keep average per subject correct in a report.

Use Aggregate Functions when average per subject must stay clear. If a simpler SQL step works, use that instead.

Aggregate Functions trap — SELECT extra column not in GROUP BY. Priya loses marks for that every viva.

Place Aggregate Functions next to nearby SQL work — GROUP BY subject is the link.

Close Aggregate Functions with: “If I skip it, average per subject goes wrong like this: SELECT extra column not in GROUP BY.”

Exam tip

Say Aggregate Functions in one breath, then GROUP BY subject, then SELECT extra column not in GROUP BY.

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

DefAggregate Functions — Priya uses it for average per subject in a report.
RuleAggregate Functions → GROUP BY subject.
RememberAggregate Functions + the query planner (a report).
UseAggregate Functions in a report (average per subject).
TrapAggregate Functions — SELECT extra column not in GROUP BY.
ExAggregate Functions → average per subject.

SQL · Theory

Aggregate Functions

← All stacks

Theory

117/439

Aggregate Functions

Aggregate Functions is simple if you keep it on paper. Priya uses it for average per subject in a report.

Aggregate Functions on the board — GROUP BY subject. Then say what average per subject looks like after.

Aggregate Functions exists so Priya can keep average per subject correct in a report.

Use Aggregate Functions when average per subject must stay clear. If a simpler SQL step works, use that instead.

Aggregate Functions trap — SELECT extra column not in GROUP BY. Priya loses marks for that every viva.

Place Aggregate Functions next to nearby SQL work — GROUP BY subject is the link.

Close Aggregate Functions with: “If I skip it, average per subject goes wrong like this: SELECT extra column not in GROUP BY.”

Exam tip

Say Aggregate Functions in one breath, then GROUP BY subject, then SELECT extra column not in GROUP BY.

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

DefAggregate Functions — Priya uses it for average per subject in a report.
RuleAggregate Functions → GROUP BY subject.
RememberAggregate Functions + the query planner (a report).
UseAggregate Functions in a report (average per subject).
TrapAggregate Functions — SELECT extra column not in GROUP BY.
ExAggregate Functions → average per subject.

Aggregate Functions

Aggregate Functions — sample query

Short notes

Questions

Aggregate Functions

Aggregate Functions — sample query

Short notes

Questions