MySQL - Count values of a column ordered by other column

Question

I want to achieve this table:

|Country|Cars|Blue Cars|Red Cars| Green Cars |
|Mexico | 22 |    12   |   8    |     2      |
|U.S.A  | 12 |    6    |   3    |     3      |
|Denmark| 10 |    3    |   2    |     5      |

That from a database table that makes a report (row) for every car, like this:

|Country|car_color|
|Mexico | Blue    |    
|U.S.A  | Red     |    
|Denmark| Blue    | 
|Denmark| Blue    |
|Mexico | Blue    |
|Denmark| Green   |
|Denmark| Red     |
|U.S.A  | Red     |
|Denmark| Green   |

I did the first part: grouping the countries and get the total number of cars by country, that was with this query:

SELECT country,
       COUNT(county)
FROM my_table
GROUP BY country
ORDER BY COUNT(country)

My question is, how do I get the color car columns in the first table after grouping the rows by county and getting the total number of cars by every country?

Note: I tried several ways but I'm failing to achieve this. As an example, I used:

SELECT country,
       COUNT(country),
       COUNT(case when car_color = 'Green' then 1 else 0 end)
FROM my_table
GROUP BY country
ORDER BY COUNT(country)

But that only shows the same value of Cars (total number of cars in a country) for the column "Green Car".

Help please!

MySQL and/or MS SQL Server? (Don't tag products not involved.) — jarlh
– jarlh, Commented May 16, 2015 at 10:46

dnoeth · Accepted Answer · 2015-05-16 09:45:38Z

3

COUNT counts non-NULL rows and your CASE always returns a value.

Either switch to SUM or omit the ELSE part:

 SELECT country
  ,COUNT(*) AS cars 
  ,SUM(case when car_color = "blue" then 1 else 0 end) AS "blue cars" 
  ,SUM(case when car_color = "red" then 1 else 0 end) AS "red cars"
  ,COUNT(case when car_color = "green" then 1 end) AS "green cars"
FROM my_table 
GROUP BY country 
ORDER BY COUNT(*) DESC

answered May 16, 2015 at 9:45

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mihai Over a year ago

In mysql this can be shorten to ..SUM(car_color = "blue" )..

dnoeth Over a year ago

@Mihai: Why should I use some proprietary syntax if there's an alternative syntax using Standard SQL. Save some keystrokes and learn non-portable SQL?

Mihai Over a year ago

Sure you`re right,but it`s good to know this is also an option.

Eduardo Sánchez Hidalgo Urías Over a year ago

Changing to SUM, with the code you wrote here, worked perfectly! Thanks dnoeth.

Gordon Linoff Over a year ago

In MySQL, the simplest way is really SUM(car_color = 'blue').

Collectives™ on Stack Overflow

MySQL - Count values of a column ordered by other column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related