월별 연령대별 통계구하기

멍멍대왕 2007. 11. 1. 10:53

SELECT CONVERT(varchar(20), jdate, 112) AS jdate,

   COUNT((CASE WHEN age / 10 = 2 THEN 1 ELSE NULL END)) '20대',
   COUNT((CASE WHEN age / 10 = 3 THEN 1 ELSE NULL END)) '30대',
   COUNT((CASE WHEN age / 10 = 4 THEN 1 ELSE NULL END)) '40대',
   COUNT((CASE WHEN age / 10 = 5 THEN 1 ELSE NULL END)) '50대',
   COUNT((CASE WHEN age / 10 = 6 THEN 1 ELSE NULL END)) '60대',
   COUNT((CASE WHEN (age / 10 > 6) OR (age / 10 < 2) THEN 1 ELSE NULL END)) '기타'
FROM (SELECT id, YEAR(GETDATE()) - ((CASE WHEN CONVERT(int, substring(ssn, 7, 1)) >= 3 THEN 2000
ELSE 1900 END) + CAST(SUBSTRING(ssn, 1, 2) AS int)) AS age,
jdate
   FROM jumin) AS A
GROUP BY CONVERT(varchar(20), jdate, 112)