Find all users with a unique last name

I have a table users.

mysql> SELECT * FROM `actors`;
+-----------+------------+------------+------------+
| actors_id | first_name | last_name  | dob        |
+-----------+------------+------------+------------+
|         1 | name1      | lastname1  | 1989-06-01 |
|         2 | name2      | lastname2  | 1989-05-02 |
|         3 | name3      | lastname2  | 1989-06-03 |
+-----------+------------+------------+------------+

I wrote a sql query that displays all users with a unique last name

SELECT MAX(a.first_name), a.last_name
FROM actors AS a
GROUP BY a.last_name
HAVING COUNT(DISTINCT a.first_name) = 1;

tell my why query removes collapsed users name2 and name3? HAVING COUNT(DISTINCT u.first_name) = 1;

How it works? help to understand how it works

Answer

The query is aggregating by last name and counting the number of users.

Run this version of the query:

SELECT MAX(a.first_name), a.last_name,
       COUNT(DISTINCT a.first_name) as num_first_names
FROM actors AS a
GROUP BY a.last_name
HAVING COUNT(DISTINCT a.first_name) = 1;

The third column is the number of first names. You’ll note that it is 1 for 'lastname1' and 2 for 'lastname2'.

The HAVING clause is keeping only the 1 value.