Question 112848 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL query with sum fields from other table, with a twist

Sorry for the vague title, but I don’t know how to word this type of problem better. Here is a simple example to explain it. I have to tables: OrderItemList and OrderHistoryLog.

OrderItemList:
|------------------------------|
| OrderNo | ItemNo | Loc | Qty |
|------------------------------|
| 100     |  A     |  1  |  1  |
| 101     |  A     |  1  |  2  |
| 102     |  A     |  1  |  1  |
| 103     |  A     |  2  |  1  |
| 104     |  A     |  2  |  1  |

OrderHistoryLog:
|------------------------------|
| OrderNo | ItemNo | Loc | Qty |
|------------------------------|
|  50     |  A     |  1  |  5  |
|  51     |  A     |  1  |  2  |
| 100     |  A     |  1  |  1  |
| 102     |  A     |  1  |  3  |
| 103     |  A     |  2  |  1  |

I need to show the records in the OrderItemList along with a LocHistQty field, which is the sum(Qty) from the OrderHistoryLog table for a given Item and Location, but only for the orders that are present in the OrderItemList.

For the above example, the result should be:

Result:
|------------------------------------------------------
| OrderNo | ItemNo | Loc | Qty | HistQty | LocHistQty |
|------------------------------|-----------------------
| 100     |  A     |  1  |  1  |   1     |     4      |
| 101     |  A     |  1  |  2  |   0     |     4      |
| 102     |  A     |  1  |  1  |   3     |     4      |
| 103     |  A     |  2  |  1  |   1     |     1      |
| 104     |  A     |  2  |  1  |   0     |     1      |

It is the last field, LocHistQty that I could use some help with. Here is what I started with (does not work):

select OI.OrderNo, OI.ItemNo, OI.Loc, OI.Qty, IFNULL(OL.Qty, 0) as HistQty, OL2.LocHistQty
from OrderItemList OI 
left join OrderItemLog OL on OL.OrderNo = OI.OrderNo and OL.ItemNo = OI.ItemNo
join 
(
    select ItemNo, Loc, sum(qty) as LocHistQty
    from zOrderItemLog 
    group by ItemNo, Loc
) as OL2 
on OL2.ItemNo = OI.ItemNo and OL2.Loc = OI.Loc 
order by OrderNo

The issue is with the above SQL is that LocHistQty contains the summary of the Qty for all orders (=11 for Loc 1 and 1 for Loc 2), not only the ones in OrderItemList.

Lastly, the real data is voluminous and query performance is important.

Help would be much appreciated.

Tags:

2

Answers


  1. 0

    The subquery can join with OrderItemList to restrict the order numbers that it sums.

    select OI.OrderNo, OI.ItemNo, OI.Loc, OI.Qty, IFNULL(OL.Qty, 0) as HistQty, OL2.LocHistQty
from OrderItemList OI 
left join OrderItemLog OL on OL.OrderNo = OI.OrderNo and OL.ItemNo = OI.ItemNo
join 
(
    select OL.ItemNo, OL.Loc, sum(OL.qty) as LocHistQty
    from OrderItemLog AS OL
    JOIN OrderItemList AS OI ON OL.OrderNo = OI.OrderNo
    group by OL.ItemNo, OL.Loc
) as OL2 
on OL2.ItemNo = OI.ItemNo and OL2.Loc = OI.Loc 
order by OrderNo

    DEMO

    Login or Signup to reply.
  2. 0

    Option 1

    SELECT 
    OrderNo,
    ItemNo,
    Loc,
    Qty,
    (SELECT 
            Qty
        FROM
            OrderHistoryLog AS A
        WHERE
            A.OrderNo = B.OrderNo AND A.Loc = B.Loc) AS HistQty,
    (SELECT 
            SUM(Qty)
        FROM
            OrderHistoryLog AS D
        WHERE
            D.OrderNo = B.OrderNo AND D.Loc = B.Loc) AS LocHistQty
FROM
    OrderItemList AS B;

    Option 2

    SELECT 
    B.OrderNo,
    B.ItemNo,
    B.Loc,
    B.Qty,
    C.Qty AS HistQty,
    (SELECT 
            SUM(Qty)
        FROM
            OrderHistoryLog AS A
        WHERE
            A.OrderNo = B.OrderNo AND A.Loc = B.Loc) AS LocHistQty
FROM
    OrderItemList AS B,
    OrderHistoryLog AS C
WHERE
    C.OrderNo = B.OrderNo AND C.Loc = B.Loc;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search