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

>I have a table like this:
>
>Sys Status
>S1 1
>S1 1
>S1 1
>S1 1
>S1 2
>S2 3
>S1 0
>S3 1
>S2 1
>S1 2
>
>where
>
>1 in the status means successful
>2 means failed
>3 means invalid
>0 means pending
>
>i would like to be able to count the status fields and group them like this:
>
>system successful failed pending invalid
>s1 4 2 1 0
>s2 1 0 0 1
>s3 1 0 0 0
>
>note that the columns successful, failed, pending, and invalid, DO NOT contain the value of the status field but rather the count of the types of values in the field. would appreciate any help you could provide. thanks!