Below is a small selection of my SQL, which is basically inventory data for my warehouse. I've had to combine two fields to create the location name (MAAISL is Aisle, and MASLOT is slot), so together they make AISLE_SLOT. Given this, how can I condense my PLT.QTY field into one line per location?
IE, in the yellow part of the example image, I'd like the "QTY.IN.LOC" to read as one line with a total of 10, rather than 3 separate lines. Basically, I want it to SUM the QTY.IN.LOC field based on all matching LOCATION fields.
Thank you!!
IE, in the yellow part of the example image, I'd like the "QTY.IN.LOC" to read as one line with a total of 10, rather than 3 separate lines. Basically, I want it to SUM the QTY.IN.LOC field based on all matching LOCATION fields.
SQL:
SELECT
CONCAT(CONCAT(LTRIM(RTRIM(MAAISL)), '_'),LTRIM(RTRIM(MASLOT))) AS LOCATION,
SUM(CASE WHEN ITUNQ3 = ' ' THEN CEIL(QTY/ITUNQ2) ELSE CEIL((QTY/ITUNQ2)/ITUNQ3) END) AS "QTY.IN.LOC"
FROM LOCXSTORER
WHERE LCSTOR = '553'
GROUP BY MAWHSE, MAAISL, MASLOT, LCSTOR, STNAME, ITDSC1, LCITEM, ITPDCD, MADEPT,ITINDP,ITINWD,ITINHI,LCLTC1
ORDER BY MAAISL ASCThank you!!
