I have a data table city like below:
| City | Population | Area |
|---|---|---|
| A | 2500000 | 800 |
| B | 990000 | 400 |
| C | 1200000 | 600 |
I need to count the number of cities for each of below conditions:
- Small: Population < 1000000 and area < 500
- Medium: 1000000 < Population < 2000000 and 500 < area < 700
- Big: Population > 2000000 and area > 700
The expected output:
| Small | Medium | Big |
|---|---|---|
| 1 | 1 | 1 |
I could only think of using CASE like this:
SELECT
CASE WHEN population < 1000000 AND area < 500 THEN COUNT(*)
WHEN population > 2000000 AND area > 700 THEN COUNT(*)
ELSE COUNT(*) END AS 'big'
FROM city;
but it only returned 1 value. Please help me to fix. Thank you.