Let’s say I have this table:
x
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?
Advertisement
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;