Medium JOINsAggregation Free

SQL interview question: COUNT DISTINCT With GROUP BY

Unique Students per Department

The dean's office wants to know how many different students each department reaches, not just how many seats it fills. A student who takes two courses in one department should count once there.

For every department in courses, return department, enrollments (the number of enrollment rows) and unique_students (the number of different students enrolled in that department's courses). Include departments with no enrollments, showing 0 for both counts. Order by unique_students descending, then department.

Skills: COUNT(DISTINCT) with GROUP BY, LEFT JOIN and how COUNT treats NULLs

Tables

courses 15 rows
  • course_id integer PK
  • course_name text
  • credits integer
  • department text
course_idcourse_namecreditsdepartment
1SQL Basics3Computer Science
2Python 1013Computer Science
3Data Structures4Computer Science
enrollments 47 rows
  • enrollment_id integer PK
  • student_id integer → students
  • course_id integer → courses
  • semester text
  • grade text
enrollment_idstudent_idcourse_idsemestergrade
111Fall 2024A
213Fall 2024A
315Fall 2024A-
About the school sample database

How to solve it

Use COUNT(DISTINCT column) inside a grouped query, for example SELECT department, COUNT(DISTINCT student_id) FROM enrollments JOIN courses USING (course_id) GROUP BY department, to count each value once per group. Plain COUNT(*) counts rows, so after a join it counts a student once for every course they take.

Why COUNT(*) gives the wrong number

COUNT(*) counts rows. After a join, a row is one enrollment, so a student who takes three courses in a department is counted three times. COUNT(DISTINCT e.student_id) collapses those repeats inside each group before it counts.

Here are both counts side by side on the sample school data:

SELECT c.department,
       COUNT(*) AS enrollments,
       COUNT(DISTINCT e.student_id) AS unique_students
FROM enrollments e
JOIN courses c ON c.course_id = e.course_id
GROUP BY c.department
ORDER BY c.department;

Computer Science has 23 enrollments but only 15 different students. Humanities has 5 enrollments and 4 students, because Maria White takes both English Literature and World History. In Mathematics, Science and Social Science the two numbers match, since nobody takes two courses in any of them. Putting both counts next to each other is a quick way to show the bug.

NULLs and the LEFT JOIN

The Arts department has one course, Art History, and nobody is enrolled in it. An inner join drops it. The practice question asks for every department, so it starts from courses and uses a LEFT JOIN, which keeps Art History with NULLs in every enrollment column.

That's where the choice of count matters again. COUNT(*) counts the padded row and reports 1 for Arts. COUNT(e.enrollment_id) and COUNT(DISTINCT e.student_id) skip NULLs, so both report 0. The rule is simple: COUNT of a column, with or without DISTINCT, ignores NULLs. If you want the zero, count a column from the right-hand table. The LEFT JOIN vs INNER JOIN page goes through the same trap with products and orders.

Distinct counts don't add up

Add up unique_students across the five departments that have enrollments and you get 38. The school has only 15 enrolled students. Most of them take courses in several departments, so each one is counted once per department they appear in.

That's correct per group, but it means you can't get a grand total by summing distinct counts. Run a separate query instead:

SELECT COUNT(DISTINCT student_id) AS enrolled_students
FROM enrollments;

It returns 15. If you're asked for a total row, compute it this way rather than summing the groups.

Counting distinct combinations

Sometimes the question is how many distinct pairs exist, for example student and course. MySQL accepts COUNT(DISTINCT student_id, course_id), but SQLite, PostgreSQL and SQL Server don't. SQLite answers with "wrong number of arguments to function COUNT()". The portable way is a subquery:

SELECT COUNT(*) AS student_course_pairs
FROM (SELECT DISTINCT student_id, course_id FROM enrollments) AS pairs;

It returns 47, the same as the number of enrollments, so no student is enrolled twice in the same course.

You'll also see people glue the columns into one string. That works only with a separator. On this data, COUNT(DISTINCT student_id || course_id) returns 46, not 47: student 1 in course 11 and student 11 in course 1 both become '111'. Adding a separator, as in student_id || '-' || course_id, gives the right 47.

Filtering on a distinct count

Put the distinct count in HAVING to keep only some groups. This finds students who take courses in at least four departments:

SELECT s.name, COUNT(DISTINCT c.department) AS departments
FROM enrollments e
JOIN courses c ON c.course_id = e.course_id
JOIN students s ON s.student_id = e.student_id
GROUP BY s.student_id, s.name
HAVING COUNT(DISTINCT c.department) >= 4
ORDER BY s.name;

Three students qualify: Alice Johnson, Carol Williams and David Brown. Maria White has the most enrollments (6) but covers only three departments, so she's left out. The same idea shows up in Unique Categories per Customer per Month, and more grouping questions are in the GROUP BY interview questions.

What to say in the interview

Say out loud what one row means after the join, then say what you want to count. If those differ, you need DISTINCT. Mention that COUNT of a column skips NULLs, that distinct counts per group don't sum to a total, and that multi-column distinct counts need a subquery outside MySQL.

Common questions

Does COUNT(DISTINCT) count NULL values?
No. COUNT(DISTINCT col) ignores NULLs, just like COUNT(col). Only COUNT(*) counts rows whatever they contain, which is why it reports 1 instead of 0 for an empty group after a LEFT JOIN.
Can you COUNT DISTINCT multiple columns?
MySQL allows COUNT(DISTINCT a, b). Elsewhere, count the rows of a subquery: SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM t) AS x. Joining the columns into one string also works, but only with a separator, or values like 1 and 11 collide.
Can I use COUNT(DISTINCT) as a window function?
Not in SQLite, PostgreSQL or SQL Server. SQLite reports "DISTINCT is not supported for window functions". Compute the distinct count in a grouped subquery and join it back to the detail rows instead.
What's the difference between SELECT DISTINCT and COUNT(DISTINCT) with GROUP BY?
SELECT DISTINCT removes duplicate output rows. COUNT(DISTINCT col) with GROUP BY keeps one row per group and tells you how many different values of col each group has.
Why don't my distinct counts per group add up to the total?
The same value can appear in several groups. On the sample school data the per-department student counts sum to 38, but only 15 different students are enrolled, because most take courses in more than one department.

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app

Other SQL dialects

  • MySQL MySQL accepts COUNT(DISTINCT col1, col2) to count distinct combinations. SQLite, PostgreSQL and SQL Server don't, so use SELECT COUNT(*) FROM (SELECT DISTINCT col1, col2 FROM t) AS x there.
  • PostgreSQL PostgreSQL can count distinct combinations with a row value, COUNT(DISTINCT (col1, col2)). Like SQLite, it doesn't allow DISTINCT inside a window function such as COUNT(DISTINCT x) OVER (...).

Keep going