Here's a variation of Michaels's code that should produce separate rows for each occupancy and separate columns for males and females.

SELECT 
  CASE WHEN r.roomtype = 1 Then 'Double' ELSE 'Single' END) AS occupancy, 
   SUM( (CASE WHEN r.sex = 'M' 1 ELSE 0 END) * 
          (CASE WHEN r.roomtype = 1 THEN 2 ELSE 1) ) AS MaleGuest,
   SUM( (CASE WHEN r.sex = 'F' 1 ELSE 0 END) *
          (CASE WHEN r.roomtype = 1 THEN 2 ELSE 1) ) AS FemaleGuest
FROM
 rooms r
WHERE
 r.hall = @building 
 and cast(r.roomNum/100 as int)=@floor -- can this be a BETWEEN?
 and r.onLine=1 
 AND r.staff=0 
 and r.periodID=4
GROUP BY (CASE WHEN r.roomtype = 1 Then 'Double' ELSE 'Single' END)

>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