>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



Thank you all for your help. Special thanks to Michael. I actually perfer it more if its returned in one recordset. :)

Thanks again