[MYSQL] Count Salary Categories

Problem

[Leetcode] Count Salary Categories

Table: Accounts

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+
account_id is the primary key (column with unique values) for this table.
Each row contains information about the monthly income for one bank account.

문제

소득 범위에 따른 계좌 수를 계산해라.
"Low Salary" : $20,000 미만
"Average Salary" : $ 20,000 ~ $ 50,000
"High Salary" : $ 50,000 초과

예제

Input: 
Accounts table:
+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+
Output: 
+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+

---

Solution

첫 시도- 오답

select category, ifnull(count(category), 0) as accounts_count
from (
select account_id, income,
        case when income < 20000 then "Low Salary"
             when income between 20000 and 50000 then "Average Salary"
             when income > 50000 then "High Salary" end "category"
from accounts) a
group by category

소득 범위를 case when으로 나눠 from절 서브쿼리로 풀면 되겠다 싶어서 해봤는데 결과는 땡.

accounts_count 가 0이 나와야 한단다.

솔루션의 도움을 받았다 🥲

두번째 시도 - 정답

select 'Low Salary' as category, count(*) as accounts_count
from Accounts
where income < 20000

union all

select 'Average Salary' as category, count(*) as accounts_count
from Accounts
where income < 50001 and income > 19999

union all

select 'High Salary' as category, count(*) as accounts_count
from Accounts
where income > 50000

이런식으로 컬럼이 아닌걸 select 해서 작성해본적이 거의 없는데,
leetcode 를 풀다보면 다들 처음보는 방식으로 문제를 해결해서, 저런식으로도 풀 수가 있구나 하고 항상 놀라는 것 같다.

쿼리 내용은 단순하지만 저렇게 풀이해서 단순화하는게 진짜 어려운 것 같다.