데이터베이스 질문과 답변
안녕하세요.
특정나이별 그룹 통계를 뽑으려고합니다.
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세 | |||
| 합계 |
by 마농
[2022.07.06 10:21:52]
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
;
by 밍밍밍밍
[2022.07.06 10:35:22]
답변감사드립니다!
) 버튼을 클릭하여 작성 하시면 됩니다.