CODEWITHSUNDEEP
sql
user3543105
user3543105

Reputation: 1

How to get count of multiple rows with distinct columns

bldg | room#
 A   |  1
 A   |  2
 A   |  3
 B   |  1
 B   |  2
 B   |  3
 B   |  4
 C   |  1
 C   |  2
 C   |  3

I'm trying to figure out which bldg has a row count = to 3 and only 3. For instance both A and C have a count of 3 , where B has a row count of 4. So my result would be...

bldg
 A    
 C

I know I'm looking at a subquery with some sort of count(distinct bldg, room#)=3 but I can't figure it out.

Thank you for your help.

Upvotes: 0

Views: 43

Answers (2)

Erran Morad
Erran Morad

Reputation: 4743

Try this - 

select bldg
from tablez
group by bldg
having count(room) = 3

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

This is a simple aggregation query with a having clause:

select bldg
from table t
group by bldg
having count(*) = 3;

Upvotes: 1

Related Questions