월별 연령대별 통계구하기

Posted at 2007. 11. 1. 10:53 | Posted in 데이타베이스/MSSQL


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)



//