Reputation: 45
Following table structure name: table
quantity price
100 30.00
50 15.00
25 25.00
25 24.00
25 23.00
I want to select the rows
WHERE SUM(quantity)<=90 AND price<26.00
the output should be
quantity price
50 15.00
25 23.00
25 24.00
I want to select all rows which are necessary to fullfill the quantity 90
WHERE and SUM doesn't seem to work
Upvotes: 1
Views: 89
Reputation: 65342
SUM()
is an aggregation function and you provide no aggregate, so this is bound to fail.
What you seem to want is a running total, this can easily be achieved:
SELECT
quantity, price,
@rt:=@rt+quantity AS runningtotal
FROM
`table`
INNER JOIN (SELECT @rt:=0) AS init ON 1=1
WHERE
price<26.00
AND @rt<=90
See the SQLfiddle
Upvotes: 1
Reputation: 16730
The problem is that you are trying to use an aggregate function in there where clause, this is not allowed. Instead you need to use HAVING clause.
Try this:
SELECT quantity, price
FROM table
GROUP BY quantity
HAVING SUM(quantity) <= 90 AND price < 26.00
EDIT
I believe what you really just need here is to get rid of the SUM function, based on your example you're not summing anything, you're just making sure that the quantity column is less than 90. Use this:
SELECT quantity, price
FROM table
WHERE quantity <= 90 AND price < 26
ORDER BY quantity DESC, price ASC // Based on your example for expected output
If you really do need the SUM function for this problem, though, the reason it won't work in the where clause is because it is an aggregate function. See the article I linked for more information.
Upvotes: 1
Reputation: 5877
You cannot use an aggregate function (SUM) in a where clause. It needs to be in a having clause.
Where price < 26
Having Sum(Quantity) <= 90
But this will only work if you have a group by statement otherwise it won't know which rows to sum
Upvotes: 1