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
```sql +------------+-------+ | 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