CODEWITHSUNDEEP
mysqlsql
Alexander Nicholas Popa
Alexander Nicholas Popa

Reputation: 272

Count distinct values where count of another value = 1

Given 

------------------------------
|id | val1 | val2 | date     |
______________________________
1     10      2     1990-10-02
2     10      3     1990-10-02
3     1       1     1990-10-02
4     21      1     1990-10-02
5     30      3     1990-10-02
6     30      1     1990-10-02

I want to have in results the row with id 3 and 4 because they have only one val1 if we group by val1.

How to achieve this? SELECT DISTINCT COUNT(*) WHERE

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can use group by and having for this:

select t.*
from t
group by val1
having count(*) = 1;

In general, I am opposed to having an aggregation query with unaggregated columns in the select. However, it is fine in this case because the count(*) guarantees that there is only one matching row.

Note: this will not work in other databases.

Upvotes: 2

Strawberry
Strawberry

Reputation: 33935

SELECT DISTINCT x.* 
           FROM my_table x 
           LEFT
           JOIN my_table y 
             ON y.id <> x.id AND y.val1 = x.val2
          WHERE y.id IS NULL;

Upvotes: 1

Related Questions