Count data in two columns 3

[BiggerBrother]

I have two tables. On is orders, and the other is despatches.

Orders:
Order ID
Product_ID
Quantity
Date

Despatches:
Despatch_ID
Order_ID
Product_ID
Despatch_Qty

I need to run a report which returns the order id of any orders where the items on the order have not all been despatched. So I may have an order with 10 widgets, and 5 were despatched in one load, and 3 in the next. So in the report, the order would be returned with the fact that two widgets are outstanding.

Is this possible in one sql statement, or do i need to test each order product one at a time?

Many thanks for any advice

BB

 

[KarveR]

Select O.Order_ID, O.Quantity , sum(D.Quantity) from Orders O, Despatches D where O.Order_ID = D.Order_ID and sum(D.Quantity) != O.Quantity;

Think that looks about right, been a long day.

Should return any order ID , order quantity and how many despatched only for orders where there is a diffenrence.


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[BiggerBrother]

many thanks for your reply. Unfortunately, it returns an error: Invalid use of group function.

Thanks again for helping

BB

 

[TonyGroves]

You need a grouping there:
[tt]
SELECT
o.order_id,o.product_id,o.quantity,
sum(d.quantity) despatched,
o.quantity-sum(d.despatch_qty) balance
FROM
orders
LEFT JOIN despatches USING (order_id,product_id)
GROUP BY o.order_id,o.product_id
HAVING balance<>0
[/tt]
This will give the order_id & product_id for each discrepancy.

 

[BiggerBrother]

That works perfectly, with only one exception. The resulting recordset only returns records which have been partly despatched. If there are no records at all for despatches for a certain order, then this order is not diplayed at all. Is it possible to return these products and orders as well?

Thanks for helping.

BB

 

[TonyGroves]

Second go (also fixing a couple of typing mistakes):
[tt]
SELECT
o.order_id,o.product_id,o.quantity,
IFNULL(SUM(d.despatch_qty),0) despatched,
o.quantity-IFNULL(SUM(d.despatch_qty),0) balance
FROM
orders o
LEFT JOIN despatches d USING (order_id,product_id)
GROUP BY o.order_id,o.product_id
HAVING balance<>0
[/tt]

 

[BiggerBrother]

WOW! Thanks so much. I need to learn alot more about the powers of SQL. Thanks again, works like a charm.

BB

 

[TonyGroves]

Hey, where are all those stars coming from? My solution wasn't [t]that[/i] spectacular! Thanks anyway.

 

[r937]

you woulda got one from me too if youda used COALESCE instead of IFNULL (minor point) and put all non-aggregates in the SELECT list into the GROUP BY (major point)



r937.com | rudy.ca