Tuesday, 13 August 2013

Efficient way to aggregate a table only considering items that have a particular entry

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