Count multiple items from the same column

[redeye]

Hi All,
I need to do a count of individual items in one column I call status. The status column contains only three values (‘Not Available’, Partially Available’, ‘Fully Available’). My sql statement below just gets me the count for the status column, I need to know how to count the status column for the specific items.

SELECT EEI.EEI_NAME, COUNT(EEI_INSTANCE_RECOVERY.STATUS) AS EXPR1
FROM MTS_EVENT INNER JOIN EEI_INSTANCE_RECOVERY
ON MTS_EVENT.EVENT_ID = EEI_INSTANCE_RECOVERY.EVENT_ID
INNER JOIN EEI_OBJECT_INSTANCE
ON EEI_INSTANCE_RECOVERY.INSTANCE_ID = EEI_OBJECT_INSTANCE.INSTANCE_ID
INNER JOIN EEI ON EEI_OBJECT_INSTANCE.EEI_ID = EEI.EEI_ID
WHERE (EEI.EEI_TYPE = 'Critical Infrastructure')
AND (EEI_INSTANCE_RECOVERY.EVENT_ID = :EVENT_ID)
GROUP BY EEI.EEI_NAME ORDER BY EEI.EEI_NAME ASC

The results from this query gets me part way there, it returns is like

EEI_NAME EXPR1
Name_one.........8
Name_two.........5

My goal is to have it return

EEI_NAME EXPR1 Not_Available Partially_Available Fully_Availavle
Name_one....... 8 ..............2 ........................2...................... 4
Name_two........5...............0 ........................0 ......................5

Thank you for any help!

[debasisdas]

Try to use COUNT and SUM with DECODE.

That will solve your problem.

[redeye]

Thank You,
It worked great.