CODEWITHSUNDEEP
mysqlsqldatabase
Alice
Alice

Reputation: 156

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

Upvotes: 1

Views: 959

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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.

Upvotes: 1

Vahid Heydarinezhad
Vahid Heydarinezhad

Reputation: 126

when you run this query

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

result:
+-------------------------+------------+----------------------------------+
| MAX(a.first_name)       | last_name  |    COUNT(DISTINCT a.first_name)  |
+-------------------------+------------+----------------------------------+
| name1                   | lastname1  |             1                    |
| name2                   | lastname2  |             2                    |
+-------------------------+------------+----------------------------------+


Now 

HAVING COUNT(DISTINCT u.first_name) = 1;

then:


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


 +-------------------------+------------+---------------------------------+
| MAX(a.first_name)       | last_name  |    COUNT(DISTINCT a.first_name)  |
+-------------------------+------------+----------------------------------+
| name1                   | lastname1  |             1                    |
+-------------------------+------------+----------------------------------+

Upvotes: 1

Related Questions