General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
I have a table of rooms. Each room is a double or single. I need a query to count the total spaces for men and women. This is what I got..
select count(roomid)*2 as cap
from rooms r
where r.hall = @building and cast(r.roomNum/100 as int)=@floor and r.roomtype=1 and r.onLine=1 AND r.staff=0 and r.periodID=4
union
select count(roomid) as cap
from rooms r
where r.hall = @building and cast(r.roomNum/100 as int)=@floor and (r.roomtype=2 or r.roomtype=3) and r.onLine=1 AND r.staff=0 and r.periodID=4
Some details... first query counts the number of doubles and each double has 2 spots hence the *2. The second part count the singles. Now this returns a cursor with 1 column and 2 rows. One row with the number of doubles and one with the number of singles. But this is both males and singles together.
Now is there any way I can get 2 columns? One column with males and one with females, and 2 rows one with double spots and one with single spots.I tried a bunch of things, like group by and having but my sql knowledge is pretty limited.
Thanks mucho
Eric
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only