counting certain records?

[TAMSIN]

Can anyone tell me how to do this...
I have one table account (account_id integer) and another table user(user_id integer, state varchar, account_id integer).
The possible states for a user are 'new','active' and 'inactive'. I want to run a query to give me the following output:
account_id new_no active_no inactive_no
1 4 3 2
2 0 1 1
etc.
I'm not sure what the most efficient way to do this would be - any hints appreciated!
Thanks

 

[THoey]

I have a more complex statement that I use to take multiple "event" records for an agent and combine them into one totals record. I modified that Oracle code that I use, but didn't test it. Try it:

SELECT DISTINCT account_id,
SUM(DECODE(LOWER(state), 'new' , 1, 0)) new_no,
SUM(DECODE(LOWER(state), 'active' , 1, 0)) active_no,
SUM(DECODE(LOWER(state), 'inactive', 1, 0)) inactive_no,
FROM user
GROUP BY account_id;

Hope it helps...

 

[TAMSIN]

Thanks - that worked.
I'm using PostgreSQL so had to change "SUM(DECODE.." to "SUM(CASE WHEN state = 'NEW' THEN 1 ELSE 0 END)", but apart from that great - just couldn't see how to do it at all before!
Thanks,
Tamsin