CODEWITHSUNDEEP
sqlgroup-byverticavsql
Analyst Guy
Analyst Guy

Reputation: 125

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.

Upvotes: 0

Views: 1798

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

Just use having:

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

Upvotes: 7

Related Questions