sql group by where statement

Question

lets say i have this table

user|group|acceptance
1   | a   | -1
2   | a   | 2
3   | b   | 1
4   | b   | 2
5   | b   | 2
6   | c   | -1

how do i get count how many users in each group have acceptance not -1 but still list the group having 0 count

so result would be like

group | count
a     | 1
b     | 3
c     | 0

thanks for the help

Answer 1

Count(Col) doesn't count NULL values. So pass a non null value where acceptance <> -1 (and it will default to passing null for the case not handled)

SELECT [group], 
       COUNT(CASE WHEN acceptance <> -1 THEN 1 END) AS [count] 
FROM tbl
GROUP BY [group]

Answer 2

SELECT [group], SUM(CASE acceptance WHEN -1 THEN 0 ELSE 1 END) AS [count]
FROM MyTable
GROUP BY [group]