Easy

Count occurrences of each last name

For each unique last name in the students table, count the number of students who have that last name. Order the results alphabetically in descending order by last name.

SCHEMA

students

+------------+------------+-----------+-----+------------+
| student_id | first_name | last_name | age | class_code |
+------------+------------+-----------+-----+------------+
| 1          | Mike       | Chaplin   | 12  | 7A         |
| 2          | Emily      | Jackson   | 12  | 7A         |
| 3          | Robert     | Jackson   | 12  | 7A         |
| ...        | ...        | ...       | ... | ...        |
+------------+------------+-----------+-----+------------+


EXPECTED OUTPUT
+------------+-------+
| last_name  | count |
+------------+-------+
| West       | 1     |
| Vidic      | 1     |
| Underhill  | 1     |
| Tydaughter | 1     |
| Smith      | 6     |
| ...        | ...   |
+------------+-------+

Use GROUP BY last_name to aggregate by last name.

Use COUNT(DISTINCT student_id) to count unique students.

SELECT last_name, COUNT(DISTINCT student_id) FROM students GROUP BY 1 ORDER BY last_name DESC

  • This field is required.