Efficient way to aggregate a table only considering items that have a
particular entry
Sorry for the clunky title. An example will explain.
Say I have a likes table, with an entry for each like
user_id  |  liked_id
--------------------
   1     |     a
   2     |     a
   1     |     b
   2     |     c
Meaning user 1 has liked items a and b, and user 2 has liked items a and c.
To get an aggregate count of likes for each item, I can do:
SELECT liked_id, COUNT(*)
  FROM likes
  GROUP BY liked_id
Is there a nice way, however, to do that but only for items that have been
liked by a particular user? So, for instance, querying on user 1, the
result I'd like is:
liked_id |  count
------------------
   a     |    2
   b     |    1
Because user 1 has liked items a and b, but not c.
The best I can think of is a JOIN or IN with a subselect:
SELECT l.liked_id, count(*)
  FROM likes l
    JOIN (
      SELECT liked_id
        FROM likes
        WHERE user_id = 1
      ) l2
      ON l.liked_id=l2.liked_id
  GROUP BY l.liked_id;
Is there a better way to roll things up when aggregating? I feel like
there might be some HAVING trickery I can do, but maybe not and it might
be a slower solution anyway.
 
No comments:
Post a Comment