Calculating Totals

Hi,
I have a query that shows an item that can have multiple rows of data
associated with it.
ITEM PRICE QTY_on_BO EXT PRICE PO No. QTY on PO
12345 36 3 108 2589
10
12345 36 3 108 8965
7
12345 36 3 108 4563
5
78945 20 5 100 1258
11
78945 20 5 100 9632
4
I am trying to total the QTY_on_BO column. The issue is that I only need to
sum (3+5 = 8) not (3+3+3+5+5 =19). Same thing with the column EXT_Price, I
only need to sum (108+100=208) not (108+108+108+100+100=524).
I would appreciate any help you could provide.
Thanks,
Kevin
P.S. here is my query:
SELECT va.item, i.description, v.vendor_name, va.buyer_code,
va.cost_amt, va.purchase_order, va.due_date, dbo.SKU.list_price_amt,
COALESCE
((SELECT SUM(order_qty -
original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
FROM dbo.ORDER_DETAIL AS od
WHERE (order_type IN ('E', 'P')) AND
(order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
(item_class NOT IN ('995', '998', '999'))
AND (item = va.item)),
0) AS BOQuantity, COALESCE
((SELECT SUM(order_qty -
original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
FROM dbo.ORDER_DETAIL AS od
WHERE (order_type IN ('E', 'P')) AND
(order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
(item_class NOT IN ('995', '998', '999'))
AND (item = va.item)),
0) * dbo.SKU.list_price_amt AS ext_bo_retail, va.qty AS PO_QTY
FROM dbo.VENDOR_ACTIVITY AS va INNER JOIN
dbo.ITEM AS i ON va.item = i.item INNER JOIN
dbo.VENDOR AS v ON va.vendor_key = v.vendor_key INNER
JOIN
dbo.SKU ON i.item = dbo.SKU.item CROSS JOIN
dbo.ORDER_DETAIL
WHERE (va.due_date > '2008-03-25')
GROUP BY va.item, i.description, va.buyer_code, va.cost_amt, va.due_date,
v.vendor_name, va.purchase_order, dbo.SKU.list_price_amt, va.qty
HAVING (COALESCE
((SELECT SUM(order_qty -
original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
FROM dbo.ORDER_DETAIL AS od
WHERE (order_type IN ('E', 'P')) AND
(order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
(item_class NOT IN ('995,''998', '999'))
AND (item = va.item)),
0) > 0)
ORDER BY COALESCE
((SELECT SUM(order_qty -
original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
FROM dbo.ORDER_DETAIL AS od
WHERE (order_type IN ('E', 'P')) AND
(order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
(item_class NOT IN ('995', '998', '999'))
AND (item = va.item)),
0) * dbo.SKU.list_price_amt DESC, va.due_dateI really could use some help with this.
Still trying to figure this out, but everytime I sum I get a sum of all the
contents of the cells instead of a sum of by item.
Please help if you can.
Thanks,
Kevin
"Kevin Eck" <keck@.titlenine.com> wrote in message
news:Olyzp$PkIHA.4120@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I have a query that shows an item that can have multiple rows of data
> associated with it.
> ITEM PRICE QTY_on_BO EXT PRICE PO No. QTY on PO
> 12345 36 3 108
> 2589 10
> 12345 36 3 108
> 8965 7
> 12345 36 3 108
> 4563 5
> 78945 20 5 100
> 1258 11
> 78945 20 5 100
> 9632 4
> I am trying to total the QTY_on_BO column. The issue is that I only need
> to sum (3+5 = 8) not (3+3+3+5+5 =19). Same thing with the column
> EXT_Price, I only need to sum (108+100=208) not (108+108+108+100+100=524).
> I would appreciate any help you could provide.
> Thanks,
> Kevin
> P.S. here is my query:
> SELECT va.item, i.description, v.vendor_name, va.buyer_code,
> va.cost_amt, va.purchase_order, va.due_date, dbo.SKU.list_price_amt,
> COALESCE
> ((SELECT SUM(order_qty -
> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
> FROM dbo.ORDER_DETAIL AS od
> WHERE (order_type IN ('E', 'P')) AND
> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
> (item_class NOT IN ('995', '998', '999'))
> AND (item = va.item)),
> 0) AS BOQuantity, COALESCE
> ((SELECT SUM(order_qty -
> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
> FROM dbo.ORDER_DETAIL AS od
> WHERE (order_type IN ('E', 'P')) AND
> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
> (item_class NOT IN ('995', '998', '999'))
> AND (item = va.item)),
> 0) * dbo.SKU.list_price_amt AS ext_bo_retail, va.qty AS PO_QTY
> FROM dbo.VENDOR_ACTIVITY AS va INNER JOIN
> dbo.ITEM AS i ON va.item = i.item INNER JOIN
> dbo.VENDOR AS v ON va.vendor_key = v.vendor_key INNER
> JOIN
> dbo.SKU ON i.item = dbo.SKU.item CROSS JOIN
> dbo.ORDER_DETAIL
> WHERE (va.due_date > '2008-03-25')
> GROUP BY va.item, i.description, va.buyer_code, va.cost_amt, va.due_date,
> v.vendor_name, va.purchase_order, dbo.SKU.list_price_amt, va.qty
> HAVING (COALESCE
> ((SELECT SUM(order_qty -
> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
> FROM dbo.ORDER_DETAIL AS od
> WHERE (order_type IN ('E', 'P')) AND
> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
> (item_class NOT IN ('995,''998', '999'))
> AND (item = va.item)),
> 0) > 0)
> ORDER BY COALESCE
> ((SELECT SUM(order_qty -
> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
> FROM dbo.ORDER_DETAIL AS od
> WHERE (order_type IN ('E', 'P')) AND
> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
> (item_class NOT IN ('995', '998', '999'))
> AND (item = va.item)),
> 0) * dbo.SKU.list_price_amt DESC, va.due_date
>
>
>|||Hi Kevin,
I've had this problem in the past and even though I came up with a solution
it is not very elegent. My solution was, for each group (e.g. your ITEM
group), extend the SQL to include a count of the number items in the group
(e.g. join (select Item, count(*) from ... group by Item) t on ...). Then
this count value can be used to divide down the overall total to get the
correct result.
I would mind coming up with a more elegent solution that perhaps uses the
scope functions (Sum(value, scope), Count(value, scope)) but I haven't
figured that out yet.
Regards,
Brian
"Kevin Eck" <keck@.titlenine.com> wrote in message
news:%23PEwzTdkIHA.748@.TK2MSFTNGP04.phx.gbl...
>I really could use some help with this.
> Still trying to figure this out, but everytime I sum I get a sum of all
> the contents of the cells instead of a sum of by item.
> Please help if you can.
> Thanks,
> Kevin
> "Kevin Eck" <keck@.titlenine.com> wrote in message
> news:Olyzp$PkIHA.4120@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> I have a query that shows an item that can have multiple rows of data
>> associated with it.
>> ITEM PRICE QTY_on_BO EXT PRICE PO No. QTY on PO
>> 12345 36 3 108 2589 10
>> 12345 36 3 108 8965 7
>> 12345 36 3 108 4563 5
>> 78945 20 5 100 1258 11
>> 78945 20 5 100 9632 4
>> I am trying to total the QTY_on_BO column. The issue is that I only need
>> to sum (3+5 = 8) not (3+3+3+5+5 =19). Same thing with the column
>> EXT_Price, I only need to sum (108+100=208) not
>> (108+108+108+100+100=524).
>> I would appreciate any help you could provide.
>> Thanks,
>> Kevin
>> P.S. here is my query:
>> SELECT va.item, i.description, v.vendor_name, va.buyer_code,
>> va.cost_amt, va.purchase_order, va.due_date, dbo.SKU.list_price_amt,
>> COALESCE
>> ((SELECT SUM(order_qty -
>> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
>> FROM dbo.ORDER_DETAIL AS od
>> WHERE (order_type IN ('E', 'P')) AND
>> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
>> (item_class NOT IN ('995', '998', '999'))
>> AND (item =>> va.item)), 0) AS BOQuantity, COALESCE
>> ((SELECT SUM(order_qty -
>> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
>> FROM dbo.ORDER_DETAIL AS od
>> WHERE (order_type IN ('E', 'P')) AND
>> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
>> (item_class NOT IN ('995', '998', '999'))
>> AND (item =>> va.item)), 0) * dbo.SKU.list_price_amt AS ext_bo_retail, va.qty AS PO_QTY
>> FROM dbo.VENDOR_ACTIVITY AS va INNER JOIN
>> dbo.ITEM AS i ON va.item = i.item INNER JOIN
>> dbo.VENDOR AS v ON va.vendor_key = v.vendor_key
>> INNER JOIN
>> dbo.SKU ON i.item = dbo.SKU.item CROSS JOIN
>> dbo.ORDER_DETAIL
>> WHERE (va.due_date > '2008-03-25')
>> GROUP BY va.item, i.description, va.buyer_code, va.cost_amt, va.due_date,
>> v.vendor_name, va.purchase_order, dbo.SKU.list_price_amt, va.qty
>> HAVING (COALESCE
>> ((SELECT SUM(order_qty -
>> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
>> FROM dbo.ORDER_DETAIL AS od
>> WHERE (order_type IN ('E', 'P')) AND
>> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
>> (item_class NOT IN ('995,''998', '999'))
>> AND (item = va.item)),
>> 0) > 0)
>> ORDER BY COALESCE
>> ((SELECT SUM(order_qty -
>> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
>> FROM dbo.ORDER_DETAIL AS od
>> WHERE (order_type IN ('E', 'P')) AND
>> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
>> (item_class NOT IN ('995', '998', '999'))
>> AND (item =>> va.item)), 0) * dbo.SKU.list_price_amt DESC, va.due_date
>>
>>
>>
>|||Found a simple solution to this: divide the price or quantity by "count(*)
over(partition by item)"
"Brian" <brian1taylor@.hotmail.com> wrote in message
news:%23cJovqFoIHA.2632@.TK2MSFTNGP04.phx.gbl...
> Hi Kevin,
> I've had this problem in the past and even though I came up with a
> solution it is not very elegent. My solution was, for each group (e.g.
> your ITEM group), extend the SQL to include a count of the number items in
> the group (e.g. join (select Item, count(*) from ... group by Item) t on
> ...). Then this count value can be used to divide down the overall total
> to get the correct result.
> I would mind coming up with a more elegent solution that perhaps uses the
> scope functions (Sum(value, scope), Count(value, scope)) but I haven't
> figured that out yet.
> Regards,
> Brian
> "Kevin Eck" <keck@.titlenine.com> wrote in message
> news:%23PEwzTdkIHA.748@.TK2MSFTNGP04.phx.gbl...
>>I really could use some help with this.
>> Still trying to figure this out, but everytime I sum I get a sum of all
>> the contents of the cells instead of a sum of by item.
>> Please help if you can.
>> Thanks,
>> Kevin
>> "Kevin Eck" <keck@.titlenine.com> wrote in message
>> news:Olyzp$PkIHA.4120@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> I have a query that shows an item that can have multiple rows of data
>> associated with it.
>> ITEM PRICE QTY_on_BO EXT PRICE PO No. QTY on PO
>> 12345 36 3 108 2589 10
>> 12345 36 3 108 8965 7
>> 12345 36 3 108 4563 5
>> 78945 20 5 100 1258 11
>> 78945 20 5 100 9632 4
>> I am trying to total the QTY_on_BO column. The issue is that I only
>> need to sum (3+5 = 8) not (3+3+3+5+5 =19). Same thing with the column
>> EXT_Price, I only need to sum (108+100=208) not
>> (108+108+108+100+100=524).
>> I would appreciate any help you could provide.
>> Thanks,
>> Kevin
>> P.S. here is my query:
>> SELECT va.item, i.description, v.vendor_name, va.buyer_code,
>> va.cost_amt, va.purchase_order, va.due_date, dbo.SKU.list_price_amt,
>> COALESCE
>> ((SELECT SUM(order_qty -
>> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
>> FROM dbo.ORDER_DETAIL AS od
>> WHERE (order_type IN ('E', 'P')) AND
>> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
>> (item_class NOT IN ('995', '998', '999'))
>> AND (item =>> va.item)), 0) AS BOQuantity, COALESCE
>> ((SELECT SUM(order_qty -
>> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
>> FROM dbo.ORDER_DETAIL AS od
>> WHERE (order_type IN ('E', 'P')) AND
>> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
>> (item_class NOT IN ('995', '998', '999'))
>> AND (item =>> va.item)), 0) * dbo.SKU.list_price_amt AS ext_bo_retail, va.qty AS
>> PO_QTY
>> FROM dbo.VENDOR_ACTIVITY AS va INNER JOIN
>> dbo.ITEM AS i ON va.item = i.item INNER JOIN
>> dbo.VENDOR AS v ON va.vendor_key = v.vendor_key
>> INNER JOIN
>> dbo.SKU ON i.item = dbo.SKU.item CROSS JOIN
>> dbo.ORDER_DETAIL
>> WHERE (va.due_date > '2008-03-25')
>> GROUP BY va.item, i.description, va.buyer_code, va.cost_amt,
>> va.due_date, v.vendor_name, va.purchase_order, dbo.SKU.list_price_amt,
>> va.qty
>> HAVING (COALESCE
>> ((SELECT SUM(order_qty -
>> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
>> FROM dbo.ORDER_DETAIL AS od
>> WHERE (order_type IN ('E', 'P')) AND
>> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
>> (item_class NOT IN ('995,''998', '999'))
>> AND (item =>> va.item)), 0) > 0)
>> ORDER BY COALESCE
>> ((SELECT SUM(order_qty -
>> original_reserve_qty - cancel_qty - shipped_qty) AS Expr1
>> FROM dbo.ORDER_DETAIL AS od
>> WHERE (order_type IN ('E', 'P')) AND
>> (order_qty - original_reserve_qty - cancel_qty - shipped_qty > 0) AND
>> (item_class NOT IN ('995', '998', '999'))
>> AND (item =>> va.item)), 0) * dbo.SKU.list_price_amt DESC, va.due_date
>>
>>
>>
>>
>