CODEWITHSUNDEEP
mysqlsql
fishmong3r
fishmong3r

Reputation: 1434

MySQL sum based on col value

I have this table:

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

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

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

I'm fairly sure that I should use case, but I have no idea how to sum them. I have something like this in my mind:

SELECT
    fruit,
    sum(a-b-c)
    CASE
        WHEN a-b-c = 'a' THEN +=1
        ELSE
    CASE
        WHEN a-b-c= 'b' THEN +=2
        ELSE
    CASE
        WHEN a-b-c= 'c' THEN +=3

    END AS sum

FROM tbl_fruits
GROUP BY fruit;

Upvotes: 5

Views: 71

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176164

You could use conditional aggregation:

SELECT fruit, SUM(CASE `a-b-c`
                     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(`a-b-c`,'a','b','c'),1,2,3)) AS total 
FROM tbl_fruits
GROUP BY fruit;

SqlFiddleDemo2

Upvotes: 7

Related Questions