Oracle SQL - Using subquery in Group By

Question

I have two queries that run fine on their own and I need to combine them. One query uses "Group By" and the other query uses a subquery.

First Query:

SELECT
    LH.AISLE AS "Aisle",
    LH.DSP_LOCN AS "Location",
    WI.TC_LPN_ID AS "iLPN",
    IC.ITEM_NAME AS "Item",
    WI.ON_HAND_QTY AS "Qty",
    (SELECT
        SUM(OLI.ORDER_QTY)
    FROM 
            ORDERS O,
            ORDER_LINE_ITEM OLI
    WHERE
        O.ORDER_ID = OLI.ORDER_ID
    AND
            O.DO_STATUS = 110
    AND
            OLI.ITEM_ID = IC.ITEM_ID) AS "Released Qty"
FROM
    WM_INVENTORY WI,
    LOCN_HDR LH,
    ITEM_CBO IC
WHERE
    WI.LOCATION_ID = LH.LOCN_ID
AND
    WI.ITEM_ID = IC.ITEM_ID
AND
    LH.ZONE = 'R'
AND
    LH.LVL = '20'
ORDER BY
    LH.DSP_LOCN;

Second query:

SELECT
    LH.AISLE AS "Aisle",
    LH.DSP_LOCN AS "Location",
    WI.TC_LPN_ID AS "iLPN",
    IC.ITEM_NAME AS "Item",
    WI.ON_HAND_QTY AS "Qty",
    SUM (WI2.ON_HAND_QTY) AS "CP Qty"
FROM
    LOCN_HDR LH,
    WM_INVENTORY WI,
    ITEM_CBO IC
    LEFT JOIN WM_INVENTORY WI2
    ON (WI2.ITEM_ID = IC.ITEM_ID AND WI2.LOCN_CLASS = 'C')
WHERE
    WI.LOCATION_ID = LH.LOCN_ID
AND
    WI.ITEM_ID = IC.ITEM_ID
AND
    LH.ZONE = 'R'
AND
    LH.LVL = '20'
GROUP BY
    LH.AISLE,
    LH.DSP_LOCN,
    WI.TC_LPN_ID,
    IC.ITEM_NAME,
    WI.ON_HAND_QTY
ORDER BY
    LH.DSP_LOCN;

I don't know how I can add the subquery to the group by so both of them work together.

Do you just mean you want to incorporate "Released Qty" into the second query? If that is the case, then you should just be able to add the sub-query after "CP Qty" - however, because you are joining to IC.ITEM_ID in your sub-query, you'll need to include IC.ITEM_ID in the GROUP BY. (I notice that you currently group by IC.ITEM_NAME and not the ITEM_ID - is that because there are multiple items with the same name, that you essentially treat as the same item, but they have different IDs?) — Craig
– Craig, Commented Nov 12, 2024 at 21:01
It's always a good idea to include some example data and an example of how you're expecting your final result to appear, to avoid confusion — Craig
– Craig, Commented Nov 12, 2024 at 21:04

Littlefoot · Accepted Answer · 2024-11-12 20:58:07Z

0

If both queries work OK and return data you need, a simple option is to use the UNION set operator.

Simplified

select ... from ... where ... --> the 1st query
union all
select ... from ... where ... --> the 2nd query

Using your code:

SELECT
    LH.AISLE AS "Aisle",
    LH.DSP_LOCN AS "Location",
    WI.TC_LPN_ID AS "iLPN",
    IC.ITEM_NAME AS "Item",
    WI.ON_HAND_QTY AS "Qty",
    (SELECT
        SUM(OLI.ORDER_QTY)
    FROM 
            ORDERS O,
            ORDER_LINE_ITEM OLI
    WHERE
        O.ORDER_ID = OLI.ORDER_ID
    AND
            O.DO_STATUS = 110
    AND
            OLI.ITEM_ID = IC.ITEM_ID) AS "Released Qty"
FROM
    WM_INVENTORY WI,
    LOCN_HDR LH,
    ITEM_CBO IC
WHERE
    WI.LOCATION_ID = LH.LOCN_ID
AND
    WI.ITEM_ID = IC.ITEM_ID
AND
    LH.ZONE = 'R'
AND
    LH.LVL = '20'
--
UNION ALL
--
SELECT
    LH.AISLE AS "Aisle",
    LH.DSP_LOCN AS "Location",
    WI.TC_LPN_ID AS "iLPN",
    IC.ITEM_NAME AS "Item",
    WI.ON_HAND_QTY AS "Qty",
    SUM (WI2.ON_HAND_QTY) AS "CP Qty"
FROM
    LOCN_HDR LH,
    WM_INVENTORY WI,
    ITEM_CBO IC
    LEFT JOIN WM_INVENTORY WI2
    ON (WI2.ITEM_ID = IC.ITEM_ID AND WI2.LOCN_CLASS = 'C')
WHERE
    WI.LOCATION_ID = LH.LOCN_ID
AND
    WI.ITEM_ID = IC.ITEM_ID
AND
    LH.ZONE = 'R'
AND
    LH.LVL = '20'
GROUP BY
    LH.AISLE,
    LH.DSP_LOCN,
    WI.TC_LPN_ID,
    IC.ITEM_NAME,
    WI.ON_HAND_QTY
ORDER BY 2;

Note that both queries have to return the same number of columns (apparently, they do) which have to match in datatype (can't tell for sure as I don't have your tables nor their description, but you should know it).

As of order by clause: it comes only at the end of the whole code (that's why there's no order by after the 1st query).

answered Nov 12, 2024 at 20:58

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Craig Over a year ago

I'm pretty sure that's not the result that the OP is going to want .... there are many common fields between the queries, so I believe OP essentially just wants to incorporate "Released Qty" into the second query

Littlefoot Over a year ago

That's how I understood it, @Craig. We'll see what the OP says.

MT0 · Accepted Answer · 2024-11-13 12:06:03Z

0

As Craig pointed out - all I had to do was to include IC.ITEM_ID in the GROUP BY.

edited Nov 13, 2024 at 12:06

MT0

173k12 gold badges70 silver badges136 bronze badges

answered Nov 12, 2024 at 22:04

Inigo CA

192 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - Using subquery in Group By

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related