I have the following SQL:
WITH MYFILTER AS (
SELECT
CASE WHEN STATE IN ('A', 'B') then
case
when (NUM/DENOM - 1.0) < -0.3 then 'Less than -30%'
when (NUM/DENOM - 1.0) >= -0.3
and (NUM/DENOM - 1.0) < -0.2 then '-30% to -20%'
when (NUM/DENOM - 1.0) >= -0.2
and (NUM/DENOM - 1.0) < -0.1 then '-20% to -10%'
when (NUM/DENOM - 1.0) >= -0.1
and (NUM/DENOM - 1.0) < 0.0 then '-10% to 0%'
when (NUM/DENOM - 1.0) >= 0.0
and (NUM/DENOM - 1.0) < 0.1 then '0% to 10%'
when (NUM/DENOM - 1.0) >= 0.1
and (NUM/DENOM - 1.0) < 0.2 then '10% to 20%'
when (NUM/DENOM - 1.0) >= 0.2
and (NUM/DENOM - 1.0) < 0.3 then '20% to 30%'
when (NUM/DENOM - 1.0) >= 0.3 THEN 'At least 30%'
end
ELSE case
when (NUM/DENOM < -0.3 then 'Less than -30%'
when (NUM/DENOM >= -0.3
and (NUM/DENOM < -0.2 then '-30% to -20%'
when (NUM/DENOM >= -0.2
and (NUM/DENOM < -0.1 then '-20% to -10%'
when (NUM/DENOM >= -0.1
and (NUM/DENOM < 0.0 then '-10% to 0%'
when (NUM/DENOM >= 0.0
and (NUM/DENOM < 0.1 then '0% to 10%'
when (NUM/DENOM >= 0.1
and (NUM/DENOM < 0.2 then '10% to 20%'
when (NUM/DENOM >= 0.2
and (NUM/DENOM < 0.3 then '20% to 30%'
when (NUM/DENOM >= 0.3 THEN 'At least 30%'
end
END AS indrange
FROM MYTABLE
WHERE DENOM <> 0 AND
YEAR = 2020 AND
MONTH = 11
)
SELECT
indrange,
count (*) AS total
FROM FILTER
GROUP BY indrange
Whenever the ratio doesn't fall into one of the ranges (say, for example, my table has no rows where NUM/DENOM - 1 > 0.3) then the result I end up with does not include "At least 30%" as a row with a 0 value. Instead, the row is is simply not present. How would I change the code so that it would still include the "At least 30%" row with a corresponding 0 value? In other words, I get this:
INDRANGE TOTAL
Less than -30% 285
-30% to -20% 1,608
-20% to -10% 7,409
-10% to 0% 164,212
0% to 10% 169,665
10% to 20% 1
But I want this:
INDRANGE TOTAL
Less than -30% 285
-30% to -20% 1,608
-20% to -10% 7,409
-10% to 0% 164,212
0% to 10% 169,665
10% to 20% 1
20% to 30% 0
At Least 30% 0
How can I do it? This is DB2.
CodePudding user response：
You can use a LEFT JOIN between the ranges and your existing CTE. For example:
with myranges (indrange) as (
select 'Less than -30%' from sysibm.sysdummy1
union all select '-30% to -20%' from sysibm.sysdummy1
union all select '-20% to -10%' from sysibm.sysdummy1
union all select '-10% to 0%' from sysibm.sysdummy1
union all select '0% to 10%' from sysibm.sysdummy1
union all select '10% to 20%' from sysibm.sysdummy1
union all select '20% to 30%' from sysibm.sysdummy1
union all select 'At Least 30%' from sysibm.sysdummy1
),
myfilter as (
-- here add you existing CTE
)
select r.indrange, count(f.indrange) AS total
from myranges r
left join myfilter f on f.indrange = r.indrange
group by r.indrange
CodePudding user response：
UNTESTED: but something like this based on my prior comments.
- added a MyRanges cte listing each range 1 time thus ensuring each is included in the final query.
- Used Union all to combine it with results from MyFilter in the final query
- subtracted 1 from count(*) results since we inflated results by 1 from myRanges union
.
WITH MYRANGES AS(
SELECT 'Less than -30%' as INDRANGE FROM DUAL UNION ALL
SELECT '-30% to -20%' as INDRANGE FROM DUAL UNION ALL
SELECT '-20% to -10%' as INDRANGE FROM DUAL UNION ALL
SELECT '-10% to 0%' as INDRANGE FROM DUAL UNION ALL
SELECT '0% to 10%' as INDRANGE FROM DUAL UNION ALL
SELECT '10% to 20%' as INDRANGE FROM DUAL UNION ALL
SELECT '20% to 30%' as INDRANGE FROM DUAL UNION ALL
SELECT 'At Least 30%' as INDRANGE FROM DUAL),
MYFILTER AS (SELECT
CASE WHEN STATE IN ('A', 'B') then
case
when (NUM/DENOM - 1.0) < -0.3 then 'Less than -30%'
when (NUM/DENOM - 1.0) >= -0.3
and (NUM/DENOM - 1.0) < -0.2 then '-30% to -20%'
when (NUM/DENOM - 1.0) >= -0.2
and (NUM/DENOM - 1.0) < -0.1 then '-20% to -10%'
when (NUM/DENOM - 1.0) >= -0.1
and (NUM/DENOM - 1.0) < 0.0 then '-10% to 0%'
when (NUM/DENOM - 1.0) >= 0.0
and (NUM/DENOM - 1.0) < 0.1 then '0% to 10%'
when (NUM/DENOM - 1.0) >= 0.1
and (NUM/DENOM - 1.0) < 0.2 then '10% to 20%'
when (NUM/DENOM - 1.0) >= 0.2
and (NUM/DENOM - 1.0) < 0.3 then '20% to 30%'
when (NUM/DENOM - 1.0) >= 0.3 THEN 'At least 30%'
end
ELSE case
when (NUM/DENOM < -0.3 then 'Less than -30%'
when (NUM/DENOM >= -0.3
and (NUM/DENOM < -0.2 then '-30% to -20%'
when (NUM/DENOM >= -0.2
and (NUM/DENOM < -0.1 then '-20% to -10%'
when (NUM/DENOM >= -0.1
and (NUM/DENOM < 0.0 then '-10% to 0%'
when (NUM/DENOM >= 0.0
and (NUM/DENOM < 0.1 then '0% to 10%'
when (NUM/DENOM >= 0.1
and (NUM/DENOM < 0.2 then '10% to 20%'
when (NUM/DENOM >= 0.2
and (NUM/DENOM < 0.3 then '20% to 30%'
when (NUM/DENOM >= 0.3 THEN 'At least 30%'
end
END AS indrange
FROM MYTABLE
WHERE DENOM <> 0 AND
YEAR = 2020 AND
MONTH = 11
),
SELECT indrange, count (*)-1 AS total
FROM (SELECT * FROM MyFilter UNION ALL
SELECT * FROM MyRanges) as MyFilterandAllRanges
GROUP BY indrange
Ok now that I've done something, I've thought about it and I can do better...
so modify the final query to be.... and we don't' have to mess with the numbers and math. We're basically taking a "master list" of the ranges and outer joining it to our totals so that all ranges are always included and only those that have a match show a total. We use coalesce to handle teh fact that some may not be there and substitute the needed 0.
SELECT MyRanges.indrange, coalesce(total,0) as total
FROM MyRanges
LEFT JOIN (SELECT count(*) total, Indrange
FROM MyFilter
GROUP BY indrange) as sub
on MyRanges.indrange = sub.indrange