CODEWITHSUNDEEP
sqloracle-sqldeveloper
cl26
cl26

Reputation: 3

Select distinct results of sum

I've been trying to find some help on this but can't seem to find any. I have data like this on my table:

ID ratios
1111 0,004
2222 0,023
2222 0,012
3333 0
4444 0
... ...

For every ID I have one or more ratio values. And my next step is to sum them all.

select ID, sum(ratios)
from table
group by ID;

Resulting in something like this:

ID sum(ratios)
1111 1
2222 1
3333 0
4444 0
... ...

Here's where I hit a wall. My goal is to check if any of the sum results goes over 1 to validate my ratios. I was trying to select distinct the sum results but can't make it work since I'm also working with the group by clause. Can someone you help me solve this?

Upvotes: 0

Views: 53

Answers (2)

Andrew Kraemer
Andrew Kraemer

Reputation: 61

If you use a having statement, which is like a WHERE statement for your aggregations, you can return all of your sums that are greater than 1.

select ID, sum(ratios)
from table
group by ID
having sum(ratios) > 1;

Upvotes: 1

Ceres
Ceres

Reputation: 3668

select ID, sum(ratios)
from table
group by ID
having sum(ratios) > 1

Having is like a where clause for group by.

Upvotes: 0

Related Questions