MySQL sum based on col value

Question

I have this table:

+----+--------+-------+ | id | fruit | abc | +----+--------+-------+ | 1 | orange | a | +----+--------+-------+ | 2 | banana | c | +----+--------+-------+ | 3 | orange | c | +----+--------+-------+ | 4 | orange | a | +----+--------+-------+ | 5 | orange | b | +----+--------+-------+ 

Now I want to list all fruits and sum based on abc values. a=1 , b=2 and c=3 . So this will be the result I want:

 +--------+-----+ | fruit | sum | +--------+-----+ | banana | 3 | +--------+-----+ | orange | 7 | +--------+-----+ 

I'm sure I should use case , but I have no idea how to summarize them. I have something like this in my head:

 SELECT fruit, sum(abc) CASE WHEN abc = 'a' THEN +=1 ELSE CASE WHEN abc= 'b' THEN +=2 ELSE CASE WHEN abc= 'c' THEN +=3 END AS sum FROM tbl_fruits GROUP BY fruit; 

Answer 1

You can use conditional aggregation:

 SELECT fruit, SUM(CASE `abc` WHEN 'a' THEN 1 WHEN 'b' THEN 2 WHEN 'c' THEN 3 END) AS total FROM tbl_fruits GROUP BY fruit; 

SqlFiddleDemo

Output:

 ╔═════════╦═══════╗ ║ fruit ║ total ║ ╠═════════╬═══════╣ ║ banana ║ 3 ║ ║ orange ║ 7 ║ ╚═════════╩═══════╝ 

---

Alternatively, using ELT/FIELD :

 SELECT fruit, SUM(ELT(FIELD(`abc`,'a','b','c'),1,2,3)) AS total FROM tbl_fruits GROUP BY fruit; 

SqlFiddleDemo2