Guys, I have 2 select can I make one?
use SQL like:

select sum(f1) from (select table1.f1 from table1 )

This is two select wich I want compare togather. Any idea?

Thanks a lot.

SELECT AreaCd, SMAssigned, SUM(ED) AS ED, SUM(HIV) AS HIV,
SUM(ED_OSS) as ED_OSS,
SUM(HIV_OSS) as HIV_OSS,
SUM(VENT) AS VENT, SUM(CPCA) AS CPCA, SUM(OSS) AS OSS, SUM(PENDED) AS PENDED,
SUM(PENDHIV) AS PENDHIV, SUM(PENDNH) AS PENDNH, SUM(PENDOTH) AS PENDOTH, SUM(EDWAIT) AS EDWAIT
FROM dbo.vwRptRosterSum
GROUP BY AreaCd, SMAssigned


CREATE VIEW dbo.vwRptRosterSum
AS
SELECT AreaCd, SMAssigned, status,
ED = CASE WHEN Status=1 THEN 1 ELSE 0 END,
HIV = CASE WHEN Status=3 THEN 1 ELSE 0 END,
ED_OSS = CASE WHEN Status=37 THEN 1 ELSE 0 END,
HIV_OSS = CASE WHEN Status=38 THEN 1 ELSE 0 END,
VENT = CASE WHEN Status=2 THEN 1 ELSE 0 END,
CPCA =CASE WHEN Status=4 THEN 1 ELSE 0 END,
OSS = CASE WHEN Status=24 THEN 1 ELSE 0 END,
PENDED = CASE WHEN (CodeClientChoice =3 and (status=26 or status=27)) then 1 ELSE 0 END ,
PENDHIV = CASE WHEN (CodeClientChoice =4 and status=27) then 1 ELSE 0 END ,
PENDNH = CASE WHEN (CodeClientChoice <3 and status=27) then 1 ELSE 0 END ,
PENDOTH = CASE WHEN (CodeClientChoice >4 and status=27) then 1 ELSE 0 END,
EDWAIT = CASE WHEN (WaitListCategory='ED' ) then 1 ELSE 0 END
FROM dbo.vwRptRosterDetailWaitingList

Thanks a lot, it is working!!!!