[LeetCode] count-salary-categories

내가 처음에 짠 쿼리는 다음과 같다 ...

SELECT category,
    COUNT(account_id) AS accounts_count
FROM (
    SELECT account_id,
        CASE
            WHEN income < 20000 THEN "Low Salary"
            WHEN income <= 50000 THEN "Average Salary"
            ELSE "High Salary"
        END AS category
    FROM Accounts
) AS temp
GROUP BY category

이렇게 짰을 떄 문제는

특정 카테고리의 count가 0인 경우 해당 row가 아예 뜨지 않는다는 거였다 ...

 

Solution을 보니까

UNION을 활용해서 각 CASE를 따로 COUNT 해주면 해결되더라 !

(SELECT
    'Low Salary' AS category,
    (SELECT COUNT(*)
    FROM accounts
    WHERE income < 20000) AS accounts_count)

UNION

(SELECT
    'Average Salary' AS category,
    (SELECT COUNT(*)
    FROM accounts
    WHERE BETWEEN 20000 AND 50000) AS accounts_count)

UNION

(SELECT
    'High Salary' AS category,
    (SELECT COUNT(*)
    FROM accounts
    WHERE income > 50000) AS accounts_count)

SELECT 문을 이용해서 table 형태를 만들어주는 방식도 요 문제에서 처음 접했다.