querying by the sum of a column in decimal

Can someone tell me how I can only return results when the total of a column is greater than 5 when the values in the column use 2 decimal places?

I have a column called earnedcredits where there may be 9 instances of .33 for a student or 10 instances of .50 for a student. I'd return results for each instance of a total >= 5 and not return anything if < 5
Thanks!!
BasssqueAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
Wouldn't HAVING work here?

select id,sum(earnedcredits) creditsum from tab1 t group by id having sum(earnedcredits) >= 5;

That doesn't work with windowing functions, but if you are doing a simple group by it should work.
0
 
slightwv (䄆 Netminder) Commented:
Can you post some sample data and expected results?

I don't understand the two decimal aspect of the requirement.

You mention SUM in the title but the description sounds more like a count.

You can use the group by having to limit only those aggregated values that you want.

If it is a count > 5 then:
select id,count(*) from sometable group by id having count(*) > 5;
0
 
sdstuberCommented:
select * from
(select t.*,sum(earnedcredits)  over(partition by student_id) creditsum from your_table)
where creditsum >= 5
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
OH, now that I see sdstuber's post, I see where the sum comes in.

Ignore my post...
0
 
slightwv (䄆 Netminder) Commented:
If you don't need information from the extra rows, maybe a simple sum based on sdstuber's post?

select * from
(
select id,sum(earnedcredits) creditsum from tab1 t group by id
)
where creditsum >= 5
/
0
 
slightwv (䄆 Netminder) Commented:
*sigh*  yes...  I diverted from my original thought and copied someone else's syntax...
0
 
BasssqueAuthor Commented:
Worked great, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.