CODEWITHSUNDEEP
mysqlsql
VikingGoat
VikingGoat

Reputation: 477

multiple counts with different values in multiple columns

Lets say I have a table like this:

+------+--------+------+
|Color |Shape   |Type  |
+------+--------+------+
|red   |square  |puzzle|
|red   |circle  |puzzle|
|green |star    |puzzle|
|green |circle  |puzzle|
|blue  |square  |puzzle|
|blue  |star    |puzzle|
|blue  |triangle|puzzle|
+------+--------+------+

and I wanted to get results that looked like this:

+--------+---------+-----------+
|redCount|blueCount|squareCount|
+--------+---------+-----------+
|2       |3        |2          |
+--------+---------+-----------+

how might I change the following query to actually work, or can it simply not be done in this manner?

SELECT COUNT(Color="blue") AS blueCount,
 COUNT(Color="red") AS redCount,
 COUNT(Shape="square") AS squareCount
 FROM toys
 WHERE Type = "puzzle";

Upvotes: 1

Views: 28

Answers (1)

potashin
potashin

Reputation: 44581

You can also try using IF 

SELECT COUNT(IF(`Color` = 'blue', `Color`, null)) AS `blueCount`
     , COUNT(IF(`Color` = 'red', `Color`, null)) AS `redCount`
     , COUNT(IF(`Shape` = 'square', `Shape`, null)) AS `squareCount`
FROM `toys`
WHERE `Type` = 'puzzle';

or CASE :

SELECT COUNT(CASE WHEN `Color` = 'blue' THEN `Color` END) AS `blueCount`
     , COUNT(CASE WHEN `Color` = 'red' THEN `Color` END) AS `redCount`
     , COUNT(CASE WHEN `Shape` = 'square' THEN `Shape` END) AS `squareCount`
FROM `toys`
WHERE `Type` = 'puzzle';

Upvotes: 1

Related Questions