title: Efficient way to aggregate a table only considering items that have a particular entry
body: <p>Sorry for the clunky title. An example will explain.</p>
  
  <p>Say I have a likes table, with an entry for each like</p>
  
  <pre><code>user_id  |  liked_id
  --------------------
     1     |     a
     2     |     a
     1     |     b
     2     |     c
  </code></pre>
  
  <p>Meaning user <code>1</code> has liked items <code>a</code> and <code>b</code>, and user <code>2</code> has liked items <code>a</code> and <code>c</code>.</p>
  
  <p>To get an aggregate count of likes for each item, I can do:</p>
  
  <pre><code>SELECT liked_id, COUNT(*)
    FROM likes
    GROUP BY liked_id
  </code></pre>
  
  <p>Is there a nice way, however, to do that but <em>only</em> for items that have been liked by a particular user? So, for instance, querying on user <code>1</code>, the result I'd like is:</p>
  
  <pre><code>liked_id |  count
  ------------------
     a     |    2
     b     |    1
  </code></pre>
  
  <p>Because user <code>1</code> has liked items <code>a</code> and <code>b</code>, but not <code>c</code>.</p>
  
  <p>The best I can think of is a <code>JOIN</code> or <code>IN</code> with a subselect:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Is there a better way to roll things up when aggregating? I feel  like there might be some <code>HAVING</code> trickery I can do, but maybe not and it might be a slower solution anyway.</p>
  
  <p>EDIT: I am using Postgres, if the tags did not make that clear.</p>
  
  <p>EDIT: Thanks for all the answers, I accepted what I thought was the best and fastest, as I asked the question - should've been obvious, really, but I gave everyone a +1.</p>
  
  <p>I should've mentioned that I needed another piece of data from the entry in the likes table so I can order on that later. The subselect will do it as will the accepted answers self join with an additional entry in the <code>SELECT</code> and <code>GROUP BY</code> parts. That'll teach me to oversimplify something for a SO question... thanks!</p>