데이터베이스 질문과 답변
with t1 as ( select '20대' as 나이, '1년미만' AS 가입일,21 AS NAI,'5' AS 가입개월수 from dual union all select '20대' as 나이, '10년미만' AS 가입일,24 AS NAI,'26' AS 가입개월수 from dual union all select '30대' as 나이, '10년미만' AS 가입일,31 AS NAI,'34' AS 가입개월수 from dual union all select '40대' as 나이, '10년미만' AS 가입일,41 AS NAI,'54' AS 가입개월수 from dual union ALL select '50대' as 나이, '10년미만' AS 가입일,51 AS NAI,'56' AS 가입개월수 from dual union ALL select '50대' as 나이, '15년미만' AS 가입일,52 AS NAI,'175' AS 가입개월수 from dual union ALL select '60대' as 나이, '20년미만' AS 가입일,61 AS NAI,'200' AS 가입개월수 from dual union ALL select '70대' as 나이, '25년미만' AS 가입일,71 AS NAI,'245' AS 가입개월수 from dual union ALL select '70대' as 나이, '25년미만' AS 가입일,76 AS NAI,'255' AS 가입개월수 from dual) select * from t1;
============================================================================
안녕하세요..어떻게 해야 할지 잘 몰라서 질문드려요ㅠ;
이런표로 쿼리를짜고싶은데요..잘안되네요;;
고수님들 간단하게라도 가르침좀요..
| 연령 | 1년미만 | 1년이상 | 5년이상 | 10년이상 | 15년이상 | 20년이상 | 합계 |
| 20대 | 1 | 1 | - | - | - | - | 2 |
| 30대 | - | - | 1 | - | - | - | 1 |
| 40대 | - | - | 1 | - | - | - | 1 |
| 50대 | - | - | - | 1 | 1 | - | 2 |
| 60대 | - | - | - | 1 | - | 1 | |
| 70대 | - | - | - | - | - | 2 | 2 |
|
|
by 김용한
[2015.08.20 11:43:24]
집계함수를 쓰시면 될듯합니다..
by jkson
[2015.08.20 11:52:34]
결과 데이터가 좀 이상한 거 같은데.. 40대에 54개월이면 5년이 안 되었는데 5년 이상에 1명이 있고..
with t1 as (
select '20대' as 나이, '1년미만' AS 가입일,21 AS NAI,'5' AS 가입개월수 from dual union all
select '20대' as 나이, '10년미만' AS 가입일,24 AS NAI,'26' AS 가입개월수 from dual union all
select '30대' as 나이, '10년미만' AS 가입일,31 AS NAI,'34' AS 가입개월수 from dual union all
select '40대' as 나이, '10년미만' AS 가입일,41 AS NAI,'54' AS 가입개월수 from dual union ALL
select '50대' as 나이, '10년미만' AS 가입일,51 AS NAI,'56' AS 가입개월수 from dual union ALL
select '50대' as 나이, '15년미만' AS 가입일,52 AS NAI,'175' AS 가입개월수 from dual union ALL
select '60대' as 나이, '20년미만' AS 가입일,61 AS NAI,'200' AS 가입개월수 from dual union ALL
select '70대' as 나이, '25년미만' AS 가입일,71 AS NAI,'245' AS 가입개월수 from dual union ALL
select '70대' as 나이, '25년미만' AS 가입일,76 AS NAI,'255' AS 가입개월수 from dual)
select "나이"
, sum(case when "가입개월수" / 12 <1 then 1 else 0 end) un1
, sum(case when "가입개월수" / 12 >= 1 and "가입개월수" / 12 < 5 then 1 else 0 end) ov1
, sum(case when "가입개월수" / 12 >= 5 and "가입개월수" / 12 < 10 then 1 else 0 end) ov5
, sum(case when "가입개월수" / 12 >= 10 and "가입개월수" / 12 < 15 then 1 else 0 end) ov10
, sum(case when "가입개월수" / 12 >= 15 and "가입개월수" / 12 < 20 then 1 else 0 end) ov15
, sum(case when "가입개월수" / 12 >= 20 then 1 else 0 end) ov20
, count(1) hap
from t1
group by "나이"
order by 1
by 안준호
[2015.08.20 12:23:09]
정말감사합니다^^
) 버튼을 클릭하여 작성 하시면 됩니다.