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

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

Method 2

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

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0