CODEWITHSUNDEEP
mysql
Kalenji
Kalenji

Reputation: 407

MySQL - Count and CASE WHEN

My google and stockoverflow searches failed me so need your help. I have a below table 

+------+------+------+------------+
| id   | Loc  | MEL  | COUNT(MEL) |
+------+------+------+------------+
|    1 | AAA  | A    |          1 |
|    2 | BBB  | B    |          1 |
|    3 | CCC  | C    |          1 |
|    4 | AAA  | D    |          1 |
|    5 | AAA  | A    |          1 |
|    6 | BBB  | B    |          1 |
|    7 | BBB  | C    |          1 |
|    8 | AAA  | D    |          1 |
+------+------+------+------------+

I want to transform it to the following table:

+------+------+------+------+
| MEL  | AAA  | BBB  | CCC  |
+------+------+------+------+
| A    |  2   |      |      |
| B    |      |   2  |      |
| C    |      |   1  |   1  |
| D    |  2   |      |   1  |
+------+------+------+------+

All conbinations of COUNT and CASE WHEN did not work?

Code to get the below tables:

CREATE TABLE Orders
(
id INT,
Loc char(255),
MEL char (10))

insert into Orders values 
(1,   "AAA", "A"),
(2,   "BBB", "B"),
(3,   "CCC", "C"),
(4,   "AAA", "D"),
(5,   "AAA", "A"),
(6,   "BBB", "B"),
(7,   "BBB", "C"),
(8,   "AAA", "D");

Upvotes: 0

Views: 99

Answers (1)

fancyPants
fancyPants

Reputation: 51888

select mel, 
sum(Loc="AAA") AS AAA, 
sum(Loc="BBB") AS BBB /* and so on */
from Orders group by mel;

You need to use sum instead of count, because the boolean expression returns 0 or 1. count doesn't care about that, it just counts, whether it's 0 or 1 doesn't matter.

Upvotes: 3

Related Questions