[mysql] 1907. Count Salary Categories - 테이블을 만들지 않고 열 추가하기

https://leetcode.com/problems/count-salary-categories/description/

위의 문제 풀이 시작

 

with tbl as(
    select account_id, income, case when
    income < 20000 then 'Low Salary'
    when income between 20000 and 50000 then 'Average Salary'
    else 'High Salary'
    end as category
    from accounts
)

select category, count(*) as accounts_count
from tbl
group by category

이미 틀렸음..ㅋㅋ 예상한 결과긴 하다

average salary값이 없으면 0으로 표기해야하는데 이걸 못한 것

그러면 with절에서 먼저 손을 봐줘야하는데..

 

그러면 애초에 high, low, average행을 하나씩 만들어서union 해주고 결과 도출하는 곳에서 count(*) -1 을 해주면 되겠다

레츠기릿 ㄱㄱ

 

 

 

with tbl as(
    select account_id, income, case when
    income < 20000 then 'Low Salary'
    when income between 20000 and 50000 then 'Average Salary'
    else 'High Salary'
    end as category
    from accounts
    UNION ALL
    SELECT 0 AS account_id, 0 AS income,'Average Salary' AS category
    UNION ALL
    SELECT 0 AS account_id, 0 AS income,'High Salary' AS category
    UNION ALL
    SELECT 0 AS account_id,0 AS income,'Low Salary' AS category
)

select category, count(*)-1 as accounts_count
from tbl
group by category;

 

근데..뭔가 더 깔끔하게 풀 수 있는 방법이 있지 않을까...?ㅜ

 

 

 

<LEARNING POINT

 테이블을 만들지 않고 새롭게 열을 추가하는 방법
- ex) SELECT 0 as id, 'High Salary' as 'category' FROM tbl 해서 union으로 붙여주면 됨