Hello there,
I have this query
SELECT * FROM (
select RangeOfLength,
report_plant Plant,
COALESCE(SUM(volume),0) vol,
COUNT(DISTINCT(Cod)) QtyCodes
from (
select CASE
WHEN b.length >= 0 and b.length <= 500 THEN '0To500'
WHEN b.length >= 501 and b.length <= 1000 THEN '501To1000'
WHEN b.length >= 1001 and b.length <= 2000 THEN '1001To2000'
WHEN b.length >= 2001 and b.length <= 3000 THEN '2001To3000'
WHEN b.length >= 3001 and b.length <= 4000 THEN '3001To4000'
WHEN b.length >= 4001 and b.length <= 5000 THEN '4001To5000'
WHEN b.length >= 5001 and b.length <= 6000 THEN '5001To6000'
WHEN b.length >= 6001 and b.length <= 7000 THEN '6001To7000'
WHEN b.length >= 7001 and b.length <= 8000 THEN '7001To8000'
WHEN b.length >= 8001 and b.length <= 99999 THEN '8001To99999'
END AS RangeOfLength,
w.report_plant,
w.volume,
y.Cod
from T56W w, T56X x, T56Y y, T57B b
where w.PART_NBR = x.PART_NBR
AND w.PLANT_ID = x.PLANT_ID
AND x.BF = y.BF
AND x.PART_NBR = y.PART_NBR
AND x.PLANT_ID = y.PLANT_ID
AND y.Cod = b.Cod
AND y.PLANT_ID = b.PLANT_ID)
GROUP BY report_plant, RangeOfLength
ORDER BY 1) Qry1
PIVOT (SUM(vol) FOR Plant
IN ('Plant 1' as "P1",
'Plant 2' as "P2",
'Plant 3' as "P3",
'Plant 4' as "P4",
'Plant 5' as "P5",
'Plant 6' as "P6",
'Plant 7' as "P7"
)) pvt
But it returns 70 rows, because I have 7 plants in my table, that is, I have 7 plants * 10 length ranges that I am evaluating in my select case, so this returns 70 rows.
I would like to get for each length range the sum of this per plant, something like shown in the image below.
But I am getting this result:
Any comments on how to fix it?
Thank you.
DS