Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to use COUNT for multiple conditions?

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.

Tags:

2

Answers


  1. 0

    You’ll want something like this:

    SELECT
    count(IIF(population < 1000000 AND area < 500, 1, NULL)) as small,
    count(IIF((population between 1000000 AND 2000000) AND (area between 500 AND 700), 1, NULL)) as medium,
    count(IIF(population > 2000000 AND area > 700, 1, NULL)) as big
    FROM cities;

    You can’t aggregate using the WHERE clause as each column requires a different condition. Instead you can use the fact that COUNT only includes the non-null values.

    Just check for each condition using the IIF function and return 1 if true or otherwise NULL. The count aggregate then correctly counts up only the rows that match the query.

    Login or Signup to reply.
  2. 0 
    SELECT
    SUM(CASE WHEN population < 1000000 AND area < 500 THEN 1 ELSE 0 END) AS Small,
    SUM(CASE WHEN population > 1000000 AND population < 2000000 AND area > 500 AND area < 700 THEN 1 ELSE 0 END) AS Medium,
    SUM(CASE WHEN population > 2000000 AND area > 700 THEN 1 ELSE 0 END) AS Big
FROM city;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search