Stumped by SUM query (and requesting help..)
-
Assume the following scenario: a customer wants 10 units A and 20 units B. In stock are (Bad English, I know
) 8 units A and 15 units B, which happen to stem from 2 different lots. From lot 1 are 10 units assigend, from lot 2 5 units. Of course a very crude simplification...I need to have output like (Order, Product, Total_Ordered_amount, Total_Assigned_amount)
ORDER 1, A, 10, 8
ORDER 1, B, 20, 15
Without using subqueries I get the following output:
ORDER 1, A, 10, 8
ORDER 1, B, 40, 15
... in which the ordered_amount of product B is doubled due to the two assignments. How can I solve this?
Thanks for any input.
Gerry S.
Used schema, data and queries
create table Orders (orders_id int, code varchar(10))
insert into orders select 1, 'ORDER 1'
create table Details (details_id int, orders_id int, productcode varchar(10), amount int)
insert into details select 1,1,'A', 10 union select 2,1,'B', 20
create table assigned (assigned_id int, details_id int, amount int)
insert into assigned select 1,1,8 union select 2,2,10 union select 3,2,5
-- desired output with undesired query
select orders.code, details.productcode, details.amount ordered_amount,
(select SUM(assigned.amount) from assigned where assigned.details_id = details.details_id ) assigned_amount
from orders join details on orders.orders_id = details.orders_id
-- wrong query
select orders.code, details.productcode, sum(details.amount) ordered_amount, sum(assigned.amount) assigned_amount
from orders join details on orders.orders_id = details.orders_id
join assigned on assigned.details_id = details.details_id
group by orders.code, details.productcode
Dutch Anti-RBAR League -
think you didn't mean to sum by amount.?..
select o.code, d.productcode, d.amount, sum(a.amount)
from orders o
join details d
on d.orders_id = o.orders_id
join assigned a
on a.details_id = d.details_id
group by o.code, d.productcode, d.amount
jon
-
Jon,
Thank you for your reply. I am afraid I oversimplified.
I need per order the total amount ordered and the total amount assigned. Product has nothing to do with it, so I should have left that out.
Is it possible to obtain the following output?
(ordercode, total_amount_ordered, total_amount_assigned)
ORDER 1, 30, 23
Gerry S.
Dutch Anti-RBAR League -
ok - you probably want the sum on your ordered amount on the result of the group by...
select code as ordercode, sum(ordered_amt) as total_amount_ordered, sum(assigned_amt) as total_amount_assigned
from(
select o.code, d.productcode, d.amount as ordered_amt, sum(a.amount) as assigned_amt
from orders o
join details d
on d.orders_id = o.orders_id
join assigned a
on a.details_id = d.details_id
group by o.code, d.productcode, d.amount
) a
group by code
-
Jon,
Aha, I see a pattern emerging. I will look for ways to extend your script (because I also need to sum on amounts delivered, amounts invoiced). Couls become quite messy
Thank you very much for your quick reply.
Gerry S.
Dutch Anti-RBAR League
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply