I think this gets you everything, except it's all on one row.
SELECT
SUM(CASE WHEN r.roomtype = 1 AND r.sex = 'M' THEN 2 ELSE 0 END) AS dblM,
SUM(CASE WHEN r.roomtype = 1 AND r.sex = 'F' THEN 2 ELSE 0 END) AS dblF,
SUM(CASE WHEN r.roomtype IN (2,3) AND r.sex = 'M' THEN 1 ELSE 0 END) AS sinM,
SUM(CASE WHEN r.roomtype IN (2,3) AND r.sex = 'F' THEN 1 ELSE 0 END) AS sinF,
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
-Mike
