How to count rows for groups with a given member?

Question

I have the following table members:

album_id | user_id
-------------------
1          1
1          2
1          4
2          1
2          4
4          1
5          3

with a unique constraint on (album_id, user_id).

I'd like to get the count of how many users there are per gallery, but only if the gallery contains a given user.

So for instance if I get all rows with user_id = 4, I'd like to get a result like

album_id | count(user_id)
-------------------------
1         3
2         2

Answer 1

SELECT album_id, count(*) AS ct
FROM   tbl t
JOIN   tbl t1 USING (album_id)
WHERE  t.user_id = 4
GROUP  BY 1
ORDER  BY 1;  -- optional

I chose this form, out of many possible solutions, because it can use an index on (user_id).

For best performance, you have two indexes: one on (user_id, album_id), and another one on (album_id) (or a multicolumn index with album as leading column). See:

• Is a composite index also good for queries on the first field?

Assuming user_id is NOT NULL, so count(*) is equivalent, but faster. See:

• Postgres: count(*) vs count(id)