Using COUNT

[Sylv4n]

Hi, The problem I have is this, I have a table as follows:

AreaID - RoomID - RoomType
1 1 A
1 2 A
1 3 A
1 4 B
1 5 C
2 1 A
2 2 B
2 3 B
3 1 A
3 2 C
4 1 A
4 2 A

And what I want is the Number of Room A's in Each Area with at least 1 room of type 'B' or 'C'

So the result would be somehing like

AreaID - NumberOfRoomA
1 3
2 1
3 1

So that 4 does not appear (as it has no B OR C)

Thanks in advance

 

[PHV]

Something like this ?
SELECT A.AreaID, Count(*) As NumberOfRoomA
FROM yourTable A
WHERE RoomType='A' AND EXISTS
(SELECT * FROM yourTable B WHERE B.AreaID=A.AreaID AND B.RoomType IN('B','C'))
GROUP BY A.AreaID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dnoeth]

Another solution:

SELECT A.AreaID,
sum(case when RoomType='A' then 1 else 0 end) As NumberOfRoomA
FROM yourTable A
GROUP BY A.AreaID
HAVING
sum(case when RoomType in ('B', 'C') then 1 else 0 end) >= 1

Dieter

 

[Sylv4n]

PHV
Thanks but I want to be able to change the minimum required from 1 to a diffrent number i.e. at least 10 B or C's (my fault for not giving this in the original Spec)

Dieter
I like this solution but MS Access 2000 does not seem to like the WHEN. I get a syntx error missing operator
any ideas?

 

[JarlH]

SELECT AreaID,
COUNT(*) AS A_Count
FROM yourTable
WHERE RoomType = 'A'
AND AreaID IN (SELECT AreaID FROM yourTable
WHERE RoomType IN ('B','C')
GROUP BY AreaID
HAVING COUNT(*) >= 10)
GROUP BY AreaID

 

[dnoeth]

This is the ANSI SQL Forum ;-)

IIRC there's IF in Access, try this instead...

Dieter

 

[PHV]

Another Way:
SELECT A.AreaID, Count(*) As NumberOfRoomA
FROM yourTable A INNER JOIN (
SELECT AreaID, Count(*) As NumberOfRoomBC FROM yourTable
WHERE RoomType In ('B','C') GROUP BY AreaID
) B ON A.AreaID=B.AreaID
WHERE A.RoomType='A' AND B.NumberOfRoomBC>=10
GROUP BY A.AreaID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244