multiple counts with different values in multiple columns

Question

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";

Does it have to be columns? Why?

Strawberry
– Strawberry

2014-05-28 18:08:15 +00:00
Commented May 28, 2014 at 18:08 — Strawberry
– Strawberry, Commented May 28, 2014 at 18:08

souslov · Accepted Answer · 2014-05-28 18:12:12Z

1

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';

edited May 28, 2014 at 18:12

answered May 28, 2014 at 18:00

souslov

44.7k11 gold badges92 silver badges113 bronze badges

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

1 Comment

echo_Me Over a year ago

you have to add ) in all your count

Collectives™ on Stack Overflow

multiple counts with different values in multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related