Skip to content
Advertisement

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

Let’s say I have this table:

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

In first phase I tried with:

Which result

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.

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:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement