通过ENUM值检索SUM(count) [英] Retrieve SUM(count) by ENUM value

问题描述

这是我的桌子的一个例子:

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

这就是我需要信息的方式.基本上是SUM(count)，但在每个路线"的列中:

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

但是这为我提供了所有IV列的0，无论如何我都不相信它是适当的-它需要按drug_id进行分组"并将这些列放在一起，我也是坚持下去.

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?

谢谢！

推荐答案

尝试一下

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

这篇关于通过ENUM值检索SUM(count)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！