Shaun Stacey Shaun Stacey - 6 months ago 12
SQL Question

Combining two different sql statements into one

I work for a warehouse for a popular retailer in the UK. I need to write an SQL statement that gives me the total number of pick locations between a given range AND the total number of empty locations between the same range.

I have managed to write queries for each but I don't know how to write them in the same query.

The first is the total number of locations.

SELECT lh.aisle, count(lh.aisle)
FROM locn_hdr lh
WHERE lh.LOCN_CLASS = 'A'
AND lh.BAY BETWEEN '0030' AND '0230'
AND lh.PICK_DETRM_ZONE LIKE 'HG%'
AND lh.AISLE <= 'QA'
GROUP BY lh.AISLE
ORDER BY lh.AISLE;


This gives the following results.

Aisle Count of Locations
I1 164
IA 164
IB 164
IC 164
ID 164


etc.... There is a long list of aisles so I've only shown the first 5.

The second SQL is

SELECT lh.AISLE, COUNT(lh.AISLE)
FROM LOCN_HDR lh
LEFT OUTER JOIN wm_inventory wi ON wi.location_id = lh.locn_id AND wi.on_hand_qty > '0'
WHERE lh.LOCN_CLASS = 'A'
AND lh.BAY BETWEEN '0030' AND '0230'
AND lh.PICK_DETRM_ZONE LIKE 'HG%'
AND lh.AISLE <= 'QA'
AND wi.location_id IS NULL
GROUP BY lh.AISLE
ORDER BY lh.AISLE;


The results for this are:

Aisle Count of Empties
I1 17
IA 54
IB 53
IC 46
ID 38


The table I want is:

Aisle Count Of locations Count of Empties
I1 164 17
IA 164 54


I'm hoping there is an easy way to do this.

Answer

One method would be to use EXISTS with a subquery in the SELECT clause:

SELECT lh.aisle, count(lh.aisle) as cnt1,
       SUM(CASE WHEN EXISTS (SELECT 1
                             FROM wm_inventory wi 
                             WHERE wi.location_id = lh.locn_id AND wi.on_hand_qty > 0
                THEN 1 ELSE 0
            END) as cnt2
FROM locn_hdr lh
WHERE lh.LOCN_CLASS = 'A' AND 
      lh.BAY BETWEEN '0030' AND '0230' AND
      lh.PICK_DETRM_ZONE LIKE 'HG%' AND
      lh.AISLE <= 'QA'
GROUP BY lh.AISLE
ORDER BY lh.AISLE;

Note: I am guessing that on_hand_qty is a number; hence, I removed the the single quotes from '0'. Numbers should be compared to numbers; strings to strings.