SQL · Theory

COUNT()()

Theory

193/439

COUNT()()

COUNT(*) how many rows (including NULL-filled rows). COUNT(marks) how many non-NULL marks. COUNT(DISTINCT city) how many unique cities.

SELECT city, COUNT(*) FROM students GROUP BY city; class size per city. HAVING COUNT(*) > 10; crowded cities.

Trap — COUNT(1) vs COUNT(*) myths — both count rows. Trap: COUNT(marks) when you meant students including absentees.

On the example next to this theory — COUNT()() — aggregate functions collapse many rows into one summary row.

Exam tip

COUNT(*) all rows. COUNT(marks) only who has marks.

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(*) rows. COUNT(col) non-NULL col. COUNT(DISTINCT …) unique.
RuleGROUP BY for counts per group.
TrapCOUNT(col) dropping NULLs when you wanted all students.

SQL · Theory

COUNT()()

← All stacks

Theory

193/439

COUNT()()

COUNT(*) how many rows (including NULL-filled rows). COUNT(marks) how many non-NULL marks. COUNT(DISTINCT city) how many unique cities.

SELECT city, COUNT(*) FROM students GROUP BY city; class size per city. HAVING COUNT(*) > 10; crowded cities.

Trap — COUNT(1) vs COUNT(*) myths — both count rows. Trap: COUNT(marks) when you meant students including absentees.

On the example next to this theory — COUNT()() — aggregate functions collapse many rows into one summary row.

Exam tip

COUNT(*) all rows. COUNT(marks) only who has marks.

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(*) rows. COUNT(col) non-NULL col. COUNT(DISTINCT …) unique.
RuleGROUP BY for counts per group.
TrapCOUNT(col) dropping NULLs when you wanted all students.

COUNT()()

COUNT()() — sample query

Short notes

Questions

COUNT()()

COUNT()() — sample query

Short notes

Questions