안녕하세요.
특정나이별 그룹 통계를 뽑으려고합니다.
1. 데이터 테이블입니다.
NO | user_name | birth | sex |
1 | 홍길동 | 440609 | M |
2 | 김철수 | 880305 | M |
3 | 김영수 | 401025 | F |
2. 열을 기준으로 나이별로 추출한 쿼리입니다.
SELECT COUNT(if(date_format(now(),'%Y')-substring(concat('19',birth),1,4) < 60 , date_format(now(),'%Y')-substring(concat('19',birth),1,4), NULL )) as age_60, COUNT(if(date_format(now(),'%Y')-substring(concat('19',birth),1,4) >= 60 AND date_format(now(),'%Y')-substring(concat('19',birth),1,4) <= 64, date_format(now(),'%Y')-substring(concat('19',birth),1,4), NULL )) as age_64, COUNT(if(date_format(now(),'%Y')-substring(concat('19',birth),1,4) >= 65 AND date_format(now(),'%Y')-substring(concat('19',birth),1,4) <= 74, date_format(now(),'%Y')-substring(concat('19',birth),1,4), NULL )) as age_74, COUNT(if(date_format(now(),'%Y')-substring(concat('19',birth),1,4) >= 75 AND date_format(now(),'%Y')-substring(concat('19',birth),1,4) <= 84, date_format(now(),'%Y')-substring(concat('19',birth),1,4), NULL )) as age_84, COUNT(if(date_format(now(),'%Y')-substring(concat('19',birth),1,4) >=85 , date_format(now(),'%Y')-substring(concat('19',birth),1,4), NULL )) as age_85, COUNT(if(sex = 'M', sex , NULL)) as male, COUNT(if(sex = 'F', sex , NULL)) as female FROM tb_table
3. 열 기준(나이별)을 행 기준으로 바꾸고 싶은데 쿼리를 어떻게 수정하면되는지 답변좀 부탁드리겠습니다.
조회하고 싶은형태입니다.
나이 | 남자(명) | 여자(명) | 합계 |
60세미만 | |||
60~64세 | |||
65~74세 | |||
75~84세 | |||
합계 |
WITH tb_table AS ( SELECT 1 no, '홍길동' user_name, '440609' birth, 'M' sex UNION ALL SELECT 2, '김철수', '880305', 'M' UNION ALL SELECT 3, '김영수', '401025', 'F' ) SELECT a.age_gb , COUNT(CASE b.sex WHEN 'M' THEN 1 END) male , COUNT(CASE b.sex WHEN 'F' THEN 1 END) female , COUNT(b.sex) tot FROM (SELECT 1 id, 0 s_age, 59 e_age, '60세미만' age_gb UNION ALL SELECT 2, 60, 64, '60~64세' UNION ALL SELECT 3, 65, 74, '65~74세' UNION ALL SELECT 4, 75, 84, '75~84세' UNION ALL SELECT 5, 85, 199, '85세이상' UNION ALL SELECT 9, 0, 199, '합계' ) a LEFT OUTER JOIN (SELECT sex , YEAR(NOW()) - CONCAT( CASE WHEN SUBSTRING(birth, 1, 2) < '30' THEN '20' ELSE '19' END , SUBSTRING(birth, 1, 2) ) + 1 age FROM tb_table ) b ON b.age BETWEEN a.s_age AND a.e_age GROUP BY a.id, a.age_gb ORDER BY a.id ;