Ahmed Ahmed - 2 months ago 11
SQL Question

Postgresql count results for returned user field

I am running the following postgres SQL query:

SELECT user_id FROM user_log WHERE date>='2016-08-09' ORDER by user_id ASC


It returns the result and groups them by user_id, so for example I can end up with multiple results from same user_id, like the example below:

user_id
1001
1001
1001
1008
1008


instead of listing each user_id, i want to just count how many results for each user_id. So for the example above I would like to know that 1001 is 3 and 1008 is 2.

Is there any way to do this directly with a SQL query?

Answer

You can try doing a simple GROUP BY query, with user_id determining the group for which you want the count:

SELECT user_id, COUNT(*) AS userCount
FROM user_log
WHERE date>='2016-08-09'
GROUP BY user_id
ORDER by user_id ASC

If you want to restrict, for example, to users only having a count of at least 3, then you can add a HAVING clause:

SELECT user_id, COUNT(*) AS userCount
FROM user_log
WHERE date>='2016-08-09'
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER by user_id ASC