CODEWITHSUNDEEP
mysqlsum
Dhanuka
Dhanuka

Reputation: 3

How to select a row according to sum of values from another column?

I have a table called stock which is designed like this:

| ID | product_id | qty |
|----|------------|-----|
|   1|       1    |  4  |
|   2|       1    |  3  |
|   3|       2    |  10 |
|   4|       2    |  15 | 
|   5|       3    |  2  |

From this table I want to select the product_id where total quantity less than 10. According to this table selection (product_id) must be

Here is what I have tried so far:

SELECT product_id 
FROM stock 
WHERE 10>(SELECT SUM(quantity) FROM stock)

Please help me figure out why this won't work.

Upvotes: 0

Views: 65

Answers (2)

AdamMc331
AdamMc331

Reputation: 16691

This won't work because if you are using an aggregate function you have to put it in the HAVING clause. You also want to group by the product_id so the SUM() function knows which groups to sum.

Try this:

SELECT product_id
FROM stock
GROUP BY product_id
HAVING SUM(quantity) < 10;

Here is the SQL Fiddle example for you.

Upvotes: 2

Haleemur Ali
Haleemur Ali

Reputation: 28233

SELECT product_id, SUM(qty) tot_stock 
FROM stock
GROUP BY 1
HAVING tot_stock < 10

This gives you the total stock as well, which you can discard if you don't need.

But, if you want to send only the product ids to the client, wrap the above query into another select like this:

SELECT product_id FROM (SELECT product_id, SUM(qty) tot_stock 
FROM stock
GROUP BY 1
HAVING tot_stock < 10) a

Upvotes: -1

Related Questions