SQL – Select all rows and count only column greater than 1

Let’s say I have this table:

name| value
----|------
 A  | 0
 B  | 0
 A  | 1 
 C  | 1
 A  | 1

The select I want is to give the result like this:

A | 2
B | 0
C | 1

In first phase I tried with:

SELECT name, count(0)
FROM table
WHERE value > 0
GROUP BY name;

Which result

A | 2
C | 1

I also want to include B with count(0) = 0. How I can do this?

Answer

You want to aggregate your rows and get one result row per name. This translates to GROUP BY name in SQL. For counting use COUNT and inside use CASE WHEN to decide what to count.

select name, count(case when value > 0 then 1 end)
from mytable
group by name
order by name;

This works because COUNT only counts non-null occurences. We could just as well use SUM for counting: sum(case when value > 0 then 1 else 0 end).

In your example there is only 0 and 1. If these are the only possible values, you can just add them up:

select name, sum(value)
from mytable
group by name
order by name;