SQL · Theory

COUNT()()

← All stacks

Theory

193/439

COUNT()()

Don’t start COUNT()() (math) with jargon. Start with average per subject. That is the whole point.

COUNT()() (math) on the board — GROUP BY subject. Then say what average per subject looks like after.

Without COUNT()() (math), a report gets messy and average per subject is hard to trust.

COUNT()() (math) shows up in a report. Name average per subject, not “a real-world scenario”.

Don’t do this with COUNT()() (math) — SELECT extra column not in GROUP BY. Interviewers spot it in ten seconds.

Place COUNT()() (math) next to nearby SQL work — GROUP BY subject is the link.

One breath for COUNT()() (math), then average per subject, then SELECT extra column not in GROUP BY. Sit down.

Exam tip

Board: average per subject. Dry-run COUNT()() (math). Name the trap: SELECT extra column not in GROUP BY.

COUNT()() — 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;

COUNT()() — aggregate functions collapse many rows into one summary row.

Short notes

DefCOUNT()() (math) — Arun uses it for average per subject in a report.
RuleCOUNT()() (math) → GROUP BY subject.
RememberCOUNT()() (math) + InnoDB (a report).
UseCOUNT()() (math) in a report (average per subject).
TrapCOUNT()() (math) — SELECT extra column not in GROUP BY.
ExCOUNT()() (math) → average per subject.

SQL · Theory

COUNT()()

← All stacks

Theory

193/439

COUNT()()

Don’t start COUNT()() (math) with jargon. Start with average per subject. That is the whole point.

COUNT()() (math) on the board — GROUP BY subject. Then say what average per subject looks like after.

Without COUNT()() (math), a report gets messy and average per subject is hard to trust.

COUNT()() (math) shows up in a report. Name average per subject, not “a real-world scenario”.

Don’t do this with COUNT()() (math) — SELECT extra column not in GROUP BY. Interviewers spot it in ten seconds.

Place COUNT()() (math) next to nearby SQL work — GROUP BY subject is the link.

One breath for COUNT()() (math), then average per subject, then SELECT extra column not in GROUP BY. Sit down.

Exam tip

Board: average per subject. Dry-run COUNT()() (math). Name the trap: SELECT extra column not in GROUP BY.

COUNT()() — 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;

COUNT()() — aggregate functions collapse many rows into one summary row.

Short notes

DefCOUNT()() (math) — Arun uses it for average per subject in a report.
RuleCOUNT()() (math) → GROUP BY subject.
RememberCOUNT()() (math) + InnoDB (a report).
UseCOUNT()() (math) in a report (average per subject).
TrapCOUNT()() (math) — SELECT extra column not in GROUP BY.
ExCOUNT()() (math) → average per subject.

COUNT()()

COUNT()() — sample query

Short notes

Questions

COUNT()()

COUNT()() — sample query

Short notes

Questions