PostgreSQL Count DISTINCT from one column when grouped by another-CodePudding

I have a single table that looks like the following (dumbed down):

userid   |   action    |    userstate
-----------------------------------------------------
1        | click       |    Maryland
2        | press       |    Delaware
3        | jog         |    New York
3        | leap        |    New York

What I'm trying to query is "number of users doing ANY action, per state"

So the result would be:

state |  users_acting
---------------------
Maryland |    1
Delaware |    1
New York |    1

Note that individual users will only be part in one state.

I can't get the mix of distinct users correct with grouping by state. I can't

SELECT DISTINCT (userid), COUNT(userid) FROM data GROUP BY state

because the distinct column needs to be in the group by, which I don't want to actually do, not to mention problems w/ the select clause.

Thanks for any thoughts.

CodePudding user response：

Just found out that there's a COUNT(DISTINCT( option which doesn't require that distinct value to be placed in the grouping clause.

SELECT COUNT(DISTINCT userid) FROM data GROUP BY state

Does the trick

CodePudding user response：

SELECT state , count(userid) FROM data GROUP BY state , userid