[leetcode] 1907. Count Salary Categories/급여 범주 구하기/CASE WHEN

1907. Count Salary Categories

 

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

Count Salary Categories - LeetCode

 

Write an SQL query to report the number of bank accounts of each salary category. The salary categories are:

• "Low Salary": All the salaries strictly less than $20000.
• "Average Salary": All the salaries in the inclusive range [$20000, $50000].
• "High Salary": All the salaries strictly greater than $50000.

The result table must contain all three categories. If there are no accounts in a category, then report 0.

 

1

# 1 - UNION
SELECT "Low Salary" AS Category,
    SUM(income<20000) AS accounts_count
FROM Accounts
UNION
SELECT  "Average Salary" Category,
    SUM(income BETWEEN 20000 AND 50000) AS accounts_count
FROM Accounts
UNION
SELECT "High Salary" category,
    SUM(income>50000) AS accounts_count
FROM Accounts

 

2

# 2 - CASE WHEN
SELECT 'Low Salary' as category
    , COUNT(CASE WHEN income<20000 then 1 end) AS accounts_count 
FROM Accounts
UNION
SELECT 'Average Salary' as category
    , COUNT(CASE WHEN income>=20000 AND income<= 50000 THEN 1 END) AS accounts_count 
FROM Accounts
UNION
SELECT 'High Salary' as category
    , COUNT(CASE WHEN income>50000 THEN 1 END) AS accounts_count 
FROM Accounts

 

 

비슷한 문제

2022.11.07 - [Data Analytics/CodingTest] - [프로그래머스] 중성화 여부 파악하기 / CASE WHEN / IF

[프로그래머스] 중성화 여부 파악하기 / CASE WHEN / IF