Retrieve SUM(count) by ENUM value

Question

This is an example of my table:

drug_id | route (enum) | count
------------------------------
1       | PO           | 2
1       | IV           | 4
1       | IV           | 6
2       | PO           | 1
2       | PO           | 5
2       | IV           | 2

This is how I need the information. Basically its SUM(count) but in a column for each "route":

drug_id | PO | IV 
-----------------
1       | 2  | 10
2       | 6  | 2

I assume I need a pivot, which I was trying to learn about, but I cannot for the life of me piece together a succinct query which will work.

I've tried things like:

SELECT drug_id, PO, IV 
FROM 
    (
        SELECT drug_id, SUM(count) as PO, '0' as IV FROM `core_reports_antiinfectives` WHERE route="PO"
        UNION
        SELECT drug_id, SUM(count) as IV, '0' as PO FROM `core_reports_antiinfectives` WHERE route="IV"
    ) aa

However this gives me 0 for ALL IV columns, and I'm not convinced that its appropriate anyway - it'll need to "group" by drug_id and put the columns together, which I am also stuck on.

Is there something I am missing? Or is there a better way to go about it?

Thanks!

Answer 1

Try this

SELECT drug_id,
        SUM(case route when 'po' then `count` else 0 end) totalPO,
        SUM(case route when 'iv' then `count` else 0 end) totalIV
FROM core_reports_antiinfectives
GROUP BY drug_id

SQLFiddle Demo