CODEWITHSUNDEEP
mysql
LIGHT
LIGHT

Reputation: 5712

mysql query to take sum from another table and select

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

Upvotes: 0

Views: 439

Answers (4)

Gian Lorenzo Abaño
Gian Lorenzo Abaño

Reputation: 662

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

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

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

Upvotes: 1

Drew
Drew

Reputation: 24960

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.

Upvotes: 1

Neeraj
Neeraj

Reputation: 316

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)

Upvotes: 1

Related Questions