I got it! I modified your statement to this:

Select Sys, COUNT(CASE Status WHEN Status=1 THEN 1 END) as successful,
COUNT(CASE Status WHEN 2 THEN 1 END) as failed,
COUNT(CASE Status WHEN 3 THEN 1 END) as invalid,
COUNT(CASE Status WHEN 0 THEN 1 END) as pending
FROM MyTable GROUP BY Sys

Again, thank you very much!


>Oops!
>
>It returned the same results under all the fields. Looks like this:
>
>Sys Successful Failed Invalid Pending
>1 2 2 2 2
>2 5 5 5 5
>3 2 2 2 2
>
>where did it go wrong?
>
>>Hi!
>>
>>Try following:
>>
>>Select Sys, COUNT(CASE WHEN Status=1 THEN 1 ELSE 0 END) as successful,
>>COUNT(CASE WHEN Status=2 THEN 1 ELSE 0 END) as failed,
>>COUNT(CASE WHEN Status=3 THEN 1 ELSE 0 END) as invalid,
>>COUNT(CASE WHEN Status=0 THEN 1 ELSE 0 END) as pending
>>FROM MyTable GROUP BY Sys
>>