mysql query to take sum from another table and select

Question

I have two tables, I want to build one query to select only results from orders where the quantity is not fulfilled by table deliveries:

table orders:

id item quantity
1  a    15
2  b    5
3  c    6

table deliveries:

id order_id quantity
1  2        3
2  2        1
3  2        1
4  3        3

I want it something like this:

SELECT * FROM `orders` 
WHERE `quantity`>(SELECT SUM(`quantity`) 
FROM `deliveries` 
WHERE `order_id`=orders.id);

result should be something like this:

id item quantity
1  a    15
3  c    6

what do you want to do? I can't understand the table. You want to get the orders that are already done? meaning if all the orders are already delivered? or the orders that are not yet completed? — Gian Lorenzo Abaño
– Gian Lorenzo Abaño, Commented Aug 21, 2016 at 2:51

Neeraj · Accepted Answer · 2016-08-21 02:50:34Z

1

If order table has only one row per order, try the below query. I think you also need to have orders for which no deliveries have been made.

SELECT 
    ord.*
FROM orders ord LEFT JOIN
  (SELECT 
    order_id, sum(quantity) AS 'totalDelivered'
   FROM 
    deliveries 
   GROUP BY order_id) 
    AS delv 
   ON ord.id = delv.order_id
WHERE (delv.totalDelivered < ord.quantity OR delv.order_id IS NULL)

answered Aug 21, 2016 at 2:50

Neeraj

3161 silver badge8 bronze badges

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

Comments

Drew · Accepted Answer · 2016-08-21 02:53:47Z

create table orders
(   id int not null,
    item varchar(100) not null,
    quantity int not null
);
insert orders values 
(1,'a',15),
(2,'b',5),
(3,'c',6);

create table deliveries
(   id int not null,
    order_id int not null,
    quantity int not null
);
insert deliveries values
(1,2,3),
(2,2,1),
(3,2,1),
(4,3,3);

query:

select o.id,o.item,o.quantity as quant_ordered,ifnull(sum(d.quantity),0) as delivered 
from orders o 
left join deliveries d 
on d.order_id=o.id 
group by o.id,o.item,o.quantity 
having delivered<quant_ordered;

+----+------+---------------+-----------+
| id | item | quant_ordered | delivered |
+----+------+---------------+-----------+
|  1 | a    |            15 |         0 |
|  3 | c    |             6 |         3 |
+----+------+---------------+-----------+

The having clause allows one to use the alias'd column name.

Shushil Bohara · Accepted Answer · 2016-08-21 02:53:49Z

1

You can try in this way also:

SELECT o.id, o.item, o.quantity
FROM orders o
INNER JOIN (SELECT 
    o1.id, 
    IFNULL(SUM(d.quantity), 0) quantity 
FROM orders o1
LEFT JOIN deliveries d ON d.order_id = o1.id
GROUP BY d.order_id) p ON p.id = o.id
AND o.quantity > p.quantity

answered Aug 21, 2016 at 2:53

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Comments

Gian Lorenzo Abaño · Accepted Answer · 2016-08-21 02:55:29Z

1

What is the use of "WHERE 'quantity'>(SELECT SUM('quantity')"? You are getting the sum of all the quantity tables so it is not possible to get one result. Your query should be something like this:

This will get all the deliveries done or completed:

SELECT orders.* FROM orders INNER JOIN deliveries ON orders.id=deliveries.order_id WHERE orders.quantity=deliveries.quantity

or

This will get all the deliveries that is not yet done or completed:

    SELECT orders.* FROM orders INNER JOIN deliveries ON orders.id=deliveries.order_id WHERE orders.quantity>deliveries.quantity

answered Aug 21, 2016 at 2:55

Gian Lorenzo Abaño

6727 silver badges21 bronze badges

Collectives™ on Stack Overflow

mysql query to take sum from another table and select

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related