>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

Eric --

You can use an IIF() or a UDF as a mini-WHERE clause on a specific SQL column:

SELECT  COUNT (DoubleRm()), COUNT (SingleRm()) 
   FROM Rooms R

FUNCTION   Double
RETURN IIF (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, 2, 0)
ENDFUNC

FUNCTION   Single
RETURN IIF (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, 1, 0)
ENDFUNC

I prefer to pass params (makes the function context independent) but that would make a pretty long function call.

That's the first half of your question -- getting the 2 rows into a single one.

I guess I'm not sure what the algorithm is that you use to distinguish rooms for guys and gals. But, if you can define that, you can use the same principle as shown. Just add the appropriate additional clauses, and expand the columns.

Jay