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