Menu
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?
361
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.
Sometimes, a query might produce too many answers and you want to limit the number of answers returned. You can do this by using the count limit search control. By default, a search does not have a count limit--it will return all answers that it finds.
The MySQL LIMIT ClauseThe LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
Since MYSQL 4.0 we can use SQL_CALC_FOUND_ROWS option in query which will tell MySQL to count total number of rows disregarding LIMIT clause. In main query add SQL_CALC_FOUND_ROWS option just after SELECT and in second query use FOUND_ROWS() function to get total number of rows without executing the query.
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.
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
