CODEWITHSUNDEEP
mysqlsqlgroup-by
KunLun
KunLun

Reputation: 3225

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?

Upvotes: 1

Views: 5005

Answers (3)

zip
zip

Reputation: 4061

Try this:

SELECT a.name, count(*)*(case when sum(a.value) >= 1 then 1 else 0 end)
FROM table a 
GROUP BY name;

For a better solution, give more details about the nature of the value column

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

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;

Upvotes: 4

GMB
GMB

Reputation: 222492

Do you just want aggregation? The following query would actually produce the correct results for your sample data:

select name, sum(value) sum_value
from mytable
group by name

Upvotes: 2

Related Questions