CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate-functions
The Fourth Hamster
The Fourth Hamster

Reputation: 55

Selecting the rows with unique values after filtering duplicates

I'm trying to get the colors that are used by only a sole individual. Using the following sample data:

+---------+-------------+
| color   | name        | 
+---------+-------------+
| red     | Jane        | 
| red     | Jane        | 
| red     | Bob         | 
| blue    | David       | 
| blue    | Bill        | 
| green   | Jack        | 
| green   | Jack        | 
| purple  | Jack        | 
| orange  | Dan         | 
+---------+-------------+

I got rid of the duplicates with

SELECT color, name FROM table GROUP BY color, name;

+---------+-------------+
| color   | name        | 
+---------+-------------+
| red     | Jane        | 
| red     | Bob         | 
| blue    | David       | 
| blue    | Bill        | 
| green   | Jack        | 
| purple  | Jack        | 
| orange  | Dan         |
+---------+-------------+

What do I need to do to filter it down further to my desired result set of green, purple, and orange only because only one person was associated with that color?

Upvotes: 2

Views: 62

Answers (1)

SandPiper
SandPiper

Reputation: 2906

You can accomplish this by going one step further in your query. Take what you have as a subquery, then use it as the source for a second subquery that counts the number of distinct names that have shown up for each color. Then, it just becomes a select from a result set where the number of unique names was one.

SELECT color
FROM (SELECT color, COUNT(name) namecount FROM (SELECT color, name 
          FROM table GROUP BY color, name) t1
      GROUP BY color) t2
WHERE namecount = 1

Upvotes: 2

Related Questions