Reputation: 143
I'm writing a query for work in Microsoft Access. The Raw table looks like this:
Columns
ColA | ColB
aa | x
aa | y
bb | x
bb | x
The problem I am trying to solve is: For each distinct value in column A, what is the count of distinct values in column B?
Output would be something like:
aa | 2
bb | 1
I would like to take it a step further and only select those where the count = 1, so :
bb | 1
would be the only result.
I have found what I consider to be a overly complicated subquery to accomplish this, but I'm hoping someone has a more elegant solution.
Thanks
Upvotes: 0
Views: 126
Reputation: 1271231
To select values of A
that have only one value of B
, you can use:
select t.A
from mytable as t
group by t.A
having min(t.B) = max(t.B);
This ignores NULL
values when considering duplicates. That can be factored in if necessary. Also, the count seems redundant, because it will always be 1.
Upvotes: 1
Reputation: 143
The solution I came up with is:
SELECT X.A, COUNT(X.B)
FROM (
SELECT D.A, D.B
FROM MY_TABLE as D
GROUP BY D.A, D.B
) AS X
GROUP BY X.A
HAVING COUNT(X.B) = 1
Upvotes: 0