데이터베이스 질문과 답변
select
Rno,
sum(case when OP = '01' then 1 else 0 end)TE,
sum(case when OP = '10' then 1 else 0 end)UJ,
sum(case when OP = '09' then 1 else 0 end)JE,
sum(case when OP = '12' then 1 else 0 end)TR,
count(1) hap
from(
select
case
when OLD_PENTOT < 1000000 then '100만 미만'
when OLD_PENTOT >= 1000000 and OLD_PENTOT < 1500000 then '100만~149만'
when OLD_PENTOT >= 1500000 and OLD_PENTOT < 2000000 then '150만~199만'
when OLD_PENTOT >= 2000000 and OLD_PENTOT < 2500000 then '200만~249만'
when OLD_PENTOT >= 2500000 and OLD_PENTOT < 3000000 then '250만~299만'
when OLD_PENTOT >= 3000000 and OLD_PENTOT < 3500000 then '300만~349만'
when OLD_PENTOT >= 3500000 and OLD_PENTOT < 4000000 then '350만~399만'
when OLD_PENTOT >= 4000000 and OLD_PENTOT < 4500000 then '400만~449만'
when OLD_PENTOT >= 4500000 and OLD_PENTOT < 5000000 then '450만~500만'
end Rno,
OLD_PEN as OP,OLD_PENTOT as OLP
from(
select
MEM_ID,
N_CODE(ID,'20141231')OLD_PEN,
N_AMTF(ID,'20141231')OLD_PENTOT
from TB_R
)where OLD_PEN in ('01','10','09','12')
)group by Rno
order by 1;
안녕하세요..
다름이아니라..
쿼리를 보시면 100만원 미만 부터 500만원 미만 까지 나와야 하는데..
100만 미만 104 48 7 19 178
100만~149만 181 16 11 1 209
150만~199만 168 5 3 0 176
200만~249만 97 0 0 0 97
250만~299만 31 0 3 0 34
300만~349만 12 0 0 0 12
350만~399만 3 0 0 0 3
이렇게 나옵니다..
400만원에서 500만원 사이인 사람이 없어서 안나오는거같은데요..
없으면 0으로 나올수 있게 하는방법 없나요?
by 창조의날개
[2016.02.16 17:59:35]
select
Rno,
sum(case when OP = '01' then 1 else 0 end) TE,
sum(case when OP = '10' then 1 else 0 end) UJ,
sum(case when OP = '09' then 1 else 0 end) JE,
sum(case when OP = '12' then 1 else 0 end) TR,
sum(hap) hap
from(
select
case
when OLD_PENTOT < 1000000 then '100만 미만'
when OLD_PENTOT >= 1000000 and OLD_PENTOT < 1500000 then '100만~149만'
when OLD_PENTOT >= 1500000 and OLD_PENTOT < 2000000 then '150만~199만'
when OLD_PENTOT >= 2000000 and OLD_PENTOT < 2500000 then '200만~249만'
when OLD_PENTOT >= 2500000 and OLD_PENTOT < 3000000 then '250만~299만'
when OLD_PENTOT >= 3000000 and OLD_PENTOT < 3500000 then '300만~349만'
when OLD_PENTOT >= 3500000 and OLD_PENTOT < 4000000 then '350만~399만'
when OLD_PENTOT >= 4000000 and OLD_PENTOT < 4500000 then '400만~449만'
when OLD_PENTOT >= 4500000 and OLD_PENTOT < 5000000 then '450만~500만'
end Rno,
OLD_PEN as OP,
1 hap
from(
select
MEM_ID,
N_CODE(ID,'20141231') OLD_PEN,
N_AMTF(ID,'20141231') OLD_PENTOT
from TB_R
)
where OLD_PEN in ('01','10','09','12')
union all
select Rno, OLD_PEN, 0 hap
from (
select '100만 미만' Rno from dual union all
select '100만~149만' from dual union all
select '150만~199만' from dual union all
select '200만~249만' from dual union all
select '250만~299만' from dual union all
select '300만~349만' from dual union all
select '350만~399만' from dual union all
select '400만~449만' from dual union all
select '450만~500만' from dual ) aa
,(
select '01' OLD_PEN from dual union all
select '09' OLD_PEN from dual union all
select '10' OLD_PEN from dual union all
select '12' OLD_PEN from dual ) bb
)
group by Rno
order by Rno
;
) 버튼을 클릭하여 작성 하시면 됩니다.