php - 行的多个 SUM

Question

我还有一个小问题。我有一个 mysql 表

cat | item |  data |
100 |   0  |   10  |
102 |   1  |   3   |
101 |   1  |   1   |
100 |   0  |   40  |
100 |   1  |   20  |
102 |   0  |   3   |
101 |   1  |   2   |
100 |   0  |   30  |
102 |   1  |   3   |

james_bond 帮助我完成了这段代码:

SELECT  cat, SUM(IF(item = 0,1,0)) AS items0,  
    SUM(IF(item = 1,1,0)) AS items1 FROM table GROUP BY cat

我得到结果:

categories |   items 0  |   items 1
100        |     3      |     1 
101        |     0      |     2 
102        |     1      |     2 

现在我需要另一个规则来获得以下结果:

我想要第 0 项和第 1 项的数据总和:

categories |   data 0  |   data 1
100        |     80    |     20 
101        |     0     |     3 
102        |     3     |     6

如果有人能帮助我，我会很高兴。

Answer 1

它应该是一个类似的查询:

SELECT 
  cat, 
  SUM(IF(item = 0,data,0)) AS data0,  
  SUM(IF(item = 1,data,0)) AS data1 
FROM table GROUP BY cat;