36. Count Salary Categories | SQL Interview Questions and Answers

[@shubhamkumar4800]

# Write your MySQL query statement below select 'Low Salary 'as category , count(account_id) as accounts_count from accounts where income < 20000 union select 'Average Salary'as category , count(account_id) as accounts_count from accounts where income between 20000 and 50000 union select 'High Salary'as category , count(account_id) as accounts_count from accounts where income > 50000 order by accounts_count desc i got the result but this one not worked , any idea why ?

[@pasumarthiashik1099]

SELECT CASE WHEN income < 20000 THEN 'Low Salary' WHEN income >= 20000 AND income

[@AbhishekYadav-g4h]

this won't work for returning the average salary row when account's count is 0. else use below code using left join : WITH Categories AS ( SELECT 'High Salary' AS category UNION ALL SELECT 'Low Salary' UNION ALL SELECT 'Average Salary' ) select c.category, coalesce(a.accounts_count, 0) as accounts_count from categories c left join (select case when income > 50000 then 'High Salary' when income < 20000 then 'Low Salary' else 'Average Salary' end as category, coalesce(count(account_id), 0) as accounts_count from Accounts group by category ) as a on c.category = a.category;

[@pukhrajrathkanthiwar2176]

madam please remove like it's irritating