0

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
J. Krajewski
  • 160
  • 2
  • 11

1 Answers1

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:

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • does it need the "AS ct" part? – J. Krajewski May 18 '22 at 00:05
  • The column alias is optional, but recommended. Choose what you like. Else you get a default name, `count` in this case. But it's recommended to include the `AS` keyword, unlike with table aliases. See: https://stackoverflow.com/a/11543614/939860 – Erwin Brandstetter May 18 '22 at 00:13