MySQL SQL - 计算具有特定值的行

SQL - counting rows with specific value

提问by ryyst

I have a table that looks somewhat like this:

我有一张看起来像这样的表：

id  value
1   0
1   1
1   2
1   0
1   1
2   2
2   1
2   1
2   0
3   0
3   2
3   0

Now for each id, I want to count the number of occurences of 0 and 1 and the number of occurences for that ID (the value can be any integer), so the end result should look something like this:

现在，对于 each id，我想计算 0 和 1 的出现次数以及该 ID 的出现次数（该值可以是任何整数），因此最终结果应如下所示：

id  n0  n1  total
1   2   2   5
2   1   2   4
3   2   0   3

I managed to get the first and last row with this statement:

我设法通过以下语句获得第一行和最后一行：

SELECT id, COUNT(*) FROM mytable GROUP BY id;

But I'm sort of lost from here. Any pointers on how to achieve this without a huge statement?

但我有点迷失在这里。关于如何在没有大量声明的情况下实现这一目标的任何指示？

回答by eggyal

With MySQL, you can use SUM(condition):

使用 MySQL，您可以使用SUM(condition)：

SELECT   id, SUM(value=0) AS n0, SUM(value=1) AS n1, COUNT(*) AS total
FROM     mytable
GROUP BY id

See it on sqlfiddle.

在sqlfiddle上查看。

回答by shawnt00

As @Zane commented above, the typical method is to use CASE expressions to perform the pivot.

正如@Zane 上面评论的那样，典型的方法是使用 CASE 表达式来执行数据透视。

SQL Server now has a PIVOT operator that you might see. DECODE() and IIF() were older approaches on Oracle and Access that you might still find lying around.

SQL Server 现在具有您可能会看到的 PIVOT 运算符。DECODE() 和 IIF() 是 Oracle 和 Access 上的旧方法，您可能仍然发现它们存在。