Count Salary Categories
Task: Calculate the number of bank accounts of each salary category. The result table must contain all three categories.
Table: Accounts
+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id | INT |
| income | INT |
+-------------+------+Input: Accounts table:
+------------+--------+
| account_id | income |
+------------+--------+
| 3 | 108939 |
| 2 | 12747 |
| 8 | 87709 |
| 6 | 91796 |
+------------+--------+Output:
+----------------+----------------+
| category | accounts_count |
+----------------+----------------+
| Average Salary | 0 |
| High Salary | 3 |
| Low Salary | 1 |
+----------------+----------------+View Reference Solution & Explanation ▼
This solution uses `UNION` to combine the result sets of multiple queries and aggregate functions to compute a single value from the data.
SELECT 'Low Salary' AS category, COUNT(*) AS accounts_count FROM Accounts WHERE income < 20000 UNION SELECT 'Average Salary' AS category, COUNT(*) AS accounts_count FROM Accounts WHERE income BETWEEN 20000 AND 50000 UNION SELECT 'High Salary' AS category, COUNT(*) AS accounts_count FROM Accounts WHERE income > 50000;