StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POsum() vs. count()
text
Body
copied!<p>Consider a voting system implemented in PostgreSQL, where each user can vote up or down on a "foo". There is a <code>foo</code> table that stores all the "foo information", and a <code>votes</code> table that stores the <code>user_id</code>, <code>foo_id</code>, and <code>vote</code>, where <code>vote</code> is +1 or -1.</p> <p>To get the vote tally for each foo, the following query would work:</p> <pre><code>SELECT sum(vote) FROM votes WHERE foo.foo_id = votes.foo_id; </code></pre> <p>But, the following would work just as well:</p> <pre><code>(SELECT count(vote) FROM votes WHERE foo.foo_id = votes.foo_id AND votes.vote = 1) - (SELECT count(vote) FROM votes WHERE foo.foo_id = votes.foo_id AND votes.vote = (-1)) </code></pre> <p>I currently have an index on <code>votes.foo_id</code>.</p> <p>Which is a more efficient approach? (In other words, which would run faster?) I'm interested in both the PostgreSQL-specific answer and the general SQL answer.</p> <p><strong>EDIT</strong></p> <p>A lot of answers have been taking into account the case where <code>vote</code> is null. I forgot to mention that there is a <code>NOT NULL</code> constraint on the vote column.</p> <p>Also, many have been pointing out that the first is much easier to read. Yes, it is definitely true, and if a colleague wrote the 2nd one, I would be exploding with rage unless there was a performance necessity. Never the less, the question is still on the performance of the two. (Technically, if the first query was <em>way</em> slower, it wouldn't be such a crime to write the second query.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload