We help IT Professionals succeed at work.

# Comparing SUMs from Different tables.

on
Medium Priority
555 Views
I would like to compare SUMs from two tables. Addtionally, I need to find the Sum from the OEOrdLin_Sql table when there is not a matching record(Item_No) in the IMLSTrx_SQL table, thus the outer join.  I am not getting the right answers with my approach.
The first table is an Order Line table. It contains Item_No and Qty_To_Ship.
Itâ€™s SUM looks like:
SELECT item_no, SUM(qty_to_ship) AS QtyToShip
FROM  OEORDLIN_SQL L
WHERE (item_no = '115-005-071')
GROUP BY item_no

The second table is a Lot Transaction table. It contains Item_No and Trx_Qty.
Itâ€™s SUM looks like:
SELECT SUM(trx_qty) AS TrxQty
FROM  IMLSTRX_SQL
WHERE (item_no = '115-005-071') AND (source = 'O') AND (ctl_no = 0)

The SUM for the OEOrdLin_SQL table is 4.
The SUM for the IMLSTrx_SQL table is 4.

Here is my attempt to compare.
SELECT L.item_no, SUM(L.qty_to_ship) AS QtyToShip, SUM(T.trx_qty) AS TrxQty
FROM  OEORDLIN_SQL L LEFT OUTER JOIN
IMLSTRX_SQL T ON L.item_no = T.item_no
WHERE (T.source = 'O') AND (T.ctl_no = 0) AND (L.item_no = '115-005-071')
GROUP BY L.item_no

My attempt yields QtyToShip = 16 and TrxQty = 20.

What is the right approach?

Thanks,
Pat

Ultimately, I need to only return those records where QtyToShip <> TrxQty so I will need to add a conditional in the Where or Having clause.
Comment
Watch Question

## View Solution Only

Commented:
try this

SELECT item_no, SUM(qty_to_ship) AS QtyToShip, sum(SELECT trx_qty AS TrxQty
FROM  IMLSTRX_SQL
WHERE (item_no = L.item_no) AND (source = 'O') AND (ctl_no = 0))
FROM  OEORDLIN_SQL L
WHERE (item_no = '115-005-071')
GROUP BY item_no

itsvtk

Commented:
Use cursor..
Get all records from Order
Get all totals from line
Get all totals for the transaction.

The transaction table needs to contact order from Order table else the item could not belong to correct order?
Commented:
Oooooops...   Try this one it works

SELECT item_no, SUM(qty_to_ship) AS QtyToShip,
(SELECT sum(trx_qty)
FROM  IMLSTRX_SQL
WHERE (item_no = L.item_no) AND (source = 'O') AND (ctl_no = 0)) AS TrxQty
FROM  OEORDLIN_SQL L
WHERE (item_no = '115-005-071')
GROUP BY item_no

itsvtk

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
It worked great. Here is the final query. I made it a Full Join. I need to catch any orders that do not have LotNos attached and I need to catch any LotNos that are not attached to an order.

SELECT     L.item_no, L.ord_no, L.line_no, SUM(ISNULL(L.qty_to_ship, 0)) AS Qty_TO_Ship, ISNULL
((SELECT     SUM(T.Trx_Qty)
FROM         IMLSTrx_SQL T
WHERE     L.ITem_No = T.Item_No AND L.Ord_No = T.Ord_No AND L.Line_No = T.Line_No AND T.Source = 'O' AND T.Ctl_No = 0), 0) AS Trx_Qty,
T.ser_lot_no
FROM         OEORDLIN_SQL L FULL OUTER JOIN
IMLSTRX_SQL T ON L.item_no = T.item_no AND L.ord_no = T.ord_no AND L.line_no = T.line_no
WHERE     (T.source = 'O') AND (T.ctl_no = 0)
GROUP BY L.item_no, L.ord_no, L.line_no, T.ser_lot_no
HAVING      (SUM(ISNULL(L.qty_to_ship, 0)) <> ISNULL
((SELECT     SUM(T.Trx_Qty)
FROM         IMLSTrx_SQL T
WHERE     L.ITem_No = T.Item_No AND L.Ord_No = T.Ord_No AND L.Line_No = T.Line_No AND T.Source = 'O' AND T.Ctl_No = 0), 0))
Access more of Experts Exchange with a free account
##### Thanks for using Experts Exchange.

Limited access with a free account allows you to:

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile