SQL-计算具有特定值的行 [英] SQL - counting rows with specific value

问题描述

我有一个看起来像这样的表:

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

现在对于每个id，我要计算0和1的出现次数以及该ID的出现次数(该值可以是任何整数)，因此最终结果应类似于以下内容:

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:

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?

推荐答案

对于MySQL，您可以使用SUM(condition):

With MySQL, you can use SUM(condition):

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

在 sqlfiddle 上查看.

这篇关于SQL-计算具有特定值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！