easyGROUP BY Aggregation Duplicate handling

Subjects Taught By Each Teacher

Count the distinct subjects each teacher teaches.

Problem statement

Prompt

Given the teacher table below, write a query that returns, for each teacher, the number of distinct subjects they teach.

Output columns: teacher_id and cnt, ordered by teacher_id.
The table may contain duplicate (teacher, subject) rows — those count once.

Example

teacher_id	subject_id	dept_id
1	2	3
1	2	4
1	3	3
2	1	1

Expected result:

teacher_id	cnt
1	2
2	1

Constraints

There is no primary key; the same (teacher_id, subject_id) can repeat.

Tips

COUNT(DISTINCT subject_id) grouped by teacher_id handles the duplicates for you.

Schema

CREATE TABLE teacher (
    teacher_id INTEGER NOT NULL,
    subject_id INTEGER NOT NULL,
    dept_id    INTEGER NOT NULL
);

Sample data

INSERT INTO teacher (teacher_id, subject_id, dept_id) VALUES
  (1, 2, 3),
  (1, 2, 4),
  (1, 3, 3),
  (2, 1, 1),
  (2, 2, 1),
  (2, 3, 1);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: distinct subjects per teacher.
SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt
FROM teacher
GROUP BY teacher_id
ORDER BY teacher_id;

Key concepts

COUNT(DISTINCT)
GROUP BY