Casy Choate Casy Choate - 1 year ago 36
MySQL Question

MySQL - How to Group by values and display in different colulmn on the same row

I have source table that I would like to sum quantities based on a specific value and display the sum for each value while grouping by an id.

animal_id oh co gender
10 5 1 M
20 10 5 F
10 15 2 F
30 5 0 C
10 5 4 M
20 10 0 F


I need an output of

animal_id Moh Mco Foh Fco Coh Cco
10 10 5 15 2 0 0
20 0 0 20 5 0 0
30 0 0 0 0 5 0


Each column will display the sum of each gender and each row will be grouped by the animal_id.

I tried to use the CASE like is MSSQL but it didn't sum per each gender.
Thinking PIVOT but I'm not very familiar with it.
I'm at a loss...

Answer Source

Try following:

SELECT animal_id, 
SUM(IF(gender='M', oh, 0)) AS Moh, SUM(IF(gender='M', co, 0)) AS Mco, 
SUM(IF(gender='F', oh, 0)) AS Foh, SUM(IF(gender='F', co, 0)) AS Fco, 
SUM(IF(gender='C', oh, 0)) AS Coh, SUM(IF(gender='C', co, 0)) AS Cco
FROM anim_table
GROUP BY animal_id;

So key concept is to combine SUM() with IF()

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download