Alan Alan - 2 months ago 15
SQL Question

SQL - count with or without subquery?

I have two tables in my DB:

Building(bno,address,bname) - PK is bno. bno
Room(bno,rno,floor,maxstud) - PK is bno,rno (together)


The Building table stands for a building number, address and name.
The Room table stands for building number, room number, floor number and maximum amount of students who can live in the room.

The query I have to write:

Find a building who has at least 10 rooms, which the maximum amount of students who can live in is 1. The columns should be bno, bname, number of such rooms.

What I wrote:

select building.bno, building.bname, count(rno)
from room natural join building
where maxstud =1
group by bno, bname
having count(rno)>=10


What the solution I have states:

with temp as (
select bno, count(distinct rno) as sumrooms
from room
where maxstud=1
group by bno
)
select bno, bname, sumrooms
from building natural join temp
where sumrooms>=10


Is my solution correct? I didn't see a reason to use a sub-query, but now I'm afraid I was wrong.

Thanks,

Alan

Answer

Your query will perform faster but I'm afraid won't compile because you are not including every unaggregated column in the GROUP BY clause (here: building.bname).

Also, the solution that you have which isn't yours counts distinct room numbers, so one may conclude that a building can have several rooms with the same numbers for example on different floors, so that a room would be identified correctly by the unique triple (bno, rno, floor).

Given what I've wrote above your query would look:

select building.bno, building.bname, count(distinct rno)
from room natural join building
where maxstud = 1
group by 1,2 -- I used positions here, you can use names if you wish
having count(distinct rno) >= 10