SQL · Theory

count()

← All stacks

Theory

118/439

count()

count() is simple if you keep it on paper. Asha uses it for average per subject in a report.

count() order — meaning → GROUP BY subject → one limit. Dumping ten features helps nobody.

Skip count() and SELECT extra column not in GROUP BY shows up in a report.

Use count() when average per subject must stay clear. If a simpler SQL step works, use that instead.

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

After count(), Asha should still remember SELECT extra column not in GROUP BY.

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

Exam tip

Board: average per subject. Dry-run count(). 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() — Asha uses it for average per subject in a report.
Rulecount() → GROUP BY subject.
Remembercount() + InnoDB (a report).
Usecount() in a report (average per subject).
Trapcount() — SELECT extra column not in GROUP BY.
Excount() → average per subject.

SQL · Theory

count()

← All stacks

Theory

118/439

count()

count() is simple if you keep it on paper. Asha uses it for average per subject in a report.

count() order — meaning → GROUP BY subject → one limit. Dumping ten features helps nobody.

Skip count() and SELECT extra column not in GROUP BY shows up in a report.

Use count() when average per subject must stay clear. If a simpler SQL step works, use that instead.

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

After count(), Asha should still remember SELECT extra column not in GROUP BY.

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

Exam tip

Board: average per subject. Dry-run count(). 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() — Asha uses it for average per subject in a report.
Rulecount() → GROUP BY subject.
Remembercount() + InnoDB (a report).
Usecount() in a report (average per subject).
Trapcount() — SELECT extra column not in GROUP BY.
Excount() → average per subject.

count()

count() — sample query

Short notes

Questions

count()

count() — sample query

Short notes

Questions