Multiple Count in one query 1

[Extension]

I need some help with one query. If you look at the query below you'll see that I have 3 sub queries which are counting records based on 3 different values. My current query doesn't output the results as expected.
I know my query is Oracle SQL but if you have a MS SQL solution, I will simply convert it.

Anyway, here's my query, the data and the results expected.

Thank you very much in advance. You're help is greatly appreciated.

SELECT USERS.STATE,
		(
		select count(USERS.STATUS)
        from   USERS
        where  USERS.STATUS = 1
		) STATUS1,
		(
		select count(USERS.STATUS)
        from   USERS
        where  USERS.STATUS = 2
		) STATUS2,
		(
		select count(USERS.STATUS)
        from   USERS
        where  USERS.STATUS = 3
		) STATUS3
		
	FROM USERS
	GROUP BY 
		USERS.STATE

USERS TBL
USER_ID \ STATE \ STATUS
20			CO		1
21			CO		2
22			AZ		2
23			AZ		2
24			FL		1
25			FL		3
26			NY		3
27			NY		3
28			MA		1

Results "expected"
STATE \ STATUS1 \ STATUS2 \ STATUS3
CO			1		1         	0
AZ			0		2			0 
FL			1		0			1
NY			0		0			3
MA			1		0			0

 

[SQLSister]

try

SELECT USERS.STATE,
	count(case when users.status = 1 then 1 else 0 end) as STATUS1,
    count(case when users.status = 2 then 1 else 0 end) as STATUS2,
    count(case when users.status = 3 then 1 else 0 end) as STATUS3           
    FROM USERS
    GROUP BY 
        USERS.STATE

"NOTHING is more important in a database than integrity." ESquared

 

[Extension]

Thanks for you help SQLSister.

With your query, I get the same number for STATUS1, STATUS2 & STATUS3.

 

[gmmastros]

Try changing SQLSister's code to SUM instead of COUNT.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Extension]

I removed the "else 0" and it's working fine. Thanks SQLSister.