CODEWITHSUNDEEP
sqlms-accessgroup-bysubquery
user1825257
user1825257

Reputation: 143

SQL - Multiple Group By statements with counts

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

Answers (2)

Gordon Linoff
Gordon Linoff

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

user1825257
user1825257

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

Related Questions