I have the following table
Col1 Col2 Col3
A1 B1 C1
A1 B1 C2
A1 B2 C1
A1 B2 C2
A1 B2 C3
A2 B1 C1
A2 B1 C2
A2 B2 C1
A2 B2 C2
From this table I want all the unique records from Col1 where for the combination of col1 and col2 there's a different count for the same value in Col1. The only possible answer is A1 in the table above.
The following query gives me the count of each col1 and col2.
select col1, col2, count(*) from table
group by col1, col2;
Col1 Col2 Count
A1 B1 2
A1 B2 3
A2 B1 2
A2 B2 2
From the above query I can see that A1 has two records with a different count. How do I return A1 in a single query?
You can use another level of aggregation:
select col1
from (select col1, col2, count(*) as cnt
from table
group by col1, col2
) t
group by col1
having min(cnt) <> max(cnt);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments