83

What I want to do is SUM a column, but also COUNT the number of rows it is summing, with a limit of no more than 5 rows. So my query is:

SELECT COUNT(*), SUM(score) FROM answers WHERE user=1 LIMIT 5

What I expected back was a COUNT(*) up to 5 (I cant just assume it will always be 5 in my code logic as it could have less than 5 answers), with a sum of the score of the up to 5 rows.

Instead what I seem to get back is the total number of matching rows (where user is 1) as the count, and the sum of the score for those rows. The numbers don't change whether I put LIMIT 1 or LIMIT 5 or even LIMIT 50.

What I believe will work in this situation is this instead

SELECT COUNT(*), SUM(score) FROM (SELECT * FROM answers WHERE user=1 LIMIT 5) AS a

But that seems a little convoluted for such a simple query, and as it's in a high traffic script, I want it to be as performant as possible.

Am I missing something? I did find this bug report from a few years back which seems to be related to this "problem", but I'm assuming it's not actually a bug?

Improve this question
3
  • 7
    LIMIT 5 will return at most 5 rows. SELECT COUNT(*), SUM(score) FROM answers will return 1 row. 1 < 5. You have it correct in the second query. Commented Jun 10, 2013 at 9:39
  • Yep @ta.speot.is , think i just needed someone to verify i wasn't doing something stupid. Could have swore there was a simpler query to do what i needed, but obviously not. Commented Jun 10, 2013 at 9:54
  • 5
    using select 1 instead of select * in nested query can theoretically lead to less memory usage Commented Sep 24, 2015 at 14:08

2 Answers 2

Reset to default
86

This is actually how your query works and is a normal behaviour. Using LIMIT you will not limit the count or sum but only the returned rows. So your query will return n rows as stated in your LIMIT clause. And since your query actually returns only one row, applying a (non-zero) limit has no effect on the results.

However, your second query will work as expected and is an established way of solving this problem.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I'll accept your answer once the SO timer lets me. But you're right, think i just needed someone else to explain it too me so i knew i wasn't doing something silly. The second query does work, and seems to be the only way to do it.
1

Optimization: select only score:

SELECT COUNT(*), SUM(score) FROM (SELECT score FROM answers WHERE user=1 LIMIT 5) AS a
Improve this answer

1 Comment

Although this code might answer the question, I recommend that you also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.