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!
Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.comICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs
It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.