CODEWITHSUNDEEP
mysqlsqljoinsum
user3780856
user3780856

Reputation: 45

SUM() in WHERE in MYSQL statement

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

Answers (4)

Eugen Rieck
Eugen Rieck

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

AdamMc331
AdamMc331

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

Cheruvian
Cheruvian

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

james
james

Reputation: 1687

Did you mean - WHERE quantity <=90 AND price<26.00

Upvotes: 2

Related Questions