sql query

[sherrysun]

Hello,
I am a newbie with ms sql. I am writing a report to retrieve data from a ms sql table. I have the following statement, in other database it will work but in ms sql, I am getting an error. Could anyone show me the correct syntax for ms sql for the same query?

select ag_id, sum(iif(status='O', 1, 0)) as O_status, sum(iif(status='C', 1, 0)) as C_status from dummy where !empty(status)
group by ag_id, status

I want to count how many items for each agent with status "O" and how many item with status "C".

thanks,

sherry

[tomhath]

select ag_id,
sum(CASE WHEN status='O' THEN 1 ELSE 0 END) as O_status, sum(CASE WHEN status='C' THEN 1 ELSE 0 END)as C_status
from dummy
where status is not null
group by ag_id, status

[sherrysun]

tomhath,
thanks for your help.
sherry