SQL - counting rows with specific value

Question

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:

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?

Answer 1

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 

See it on sqlfiddle.