GROUP BY - 기준으로 자른 후 통계 내기

1. 성으로 잘라서 몇 명의 사람이 존재하는지 계산하기

SELECT SUBSTRING(name, 1, 1) AS label,
         COUNT(*)
　FROM Persons
 GROUP BY SUBSTRING(name, 1, 1);

2. 나이로 자른 후 통계 내기

SELECT CASE WHEN age < 20 THEN '어린이'
            WHEN age BETWEEN 20 AND 69 THEN '성인'
            WHEN age >= 70 THEN '노인'
       ELSE NULL END AS age_class,
       COUNT(*)
　FROM Persons
 GROUP BY CASE WHEN age < 20 THEN '어린이'
               WHEN age BETWEEN 20 AND 69 THEN '성인'
               WHEN age >= 70 THEN '노인'
          ELSE NULL END;

3. 나이로 자른 후 통계 내기2 - partition by

SELECT name,
       age,
       CASE WHEN age < 20 THEN '어린이'
            WHEN age BETWEEN 20 AND 69 THEN '성인'
            WHEN age >= 70 THEN '노인'
       ELSE NULL END AS age_class,
       RANK() OVER(PARTITION BY CASE WHEN age < 20 THEN '어린이'
                                     WHEN age BETWEEN 20 AND 69 THEN '성인'
                                     WHEN age >= 70 THEN '노인'
                                ELSE NULL END
                       ORDER BY age) AS age_rank_in_class
　FROM Persons
 ORDER BY age;