sqlgroup-byverticavsql

SQL SUM only when each value within a group is greater than 0

Here is a sample data set:

ID   Value
1   421
1   532
1   642
2   3413
2   0
2   5323

I want a query that, in this case, only sums ID=1 because all of its values are greater than 0. I cannot use a WHERE statement that says WHERE Value > 0 because then ID=2 would still return a value. I feel like this may be an instance where I could possibly use a OVER(PARTITION BY...) statement, but I am not familiar enough to use it creatively.

As an aside, I don't simply add a WHERE ID = 1 statement because this needs to cover a much larger data set.

Solution

  • Just use having:

    select id, sum(value)
from t
group by id
having min(value) > 0;