Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

querying by the sum of a column in decimal

Posted on 2016-10-14
Medium Priority
96 Views
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!!
0
Question by:Basssque
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 77

Expert Comment

ID: 41843552
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

LVL 74

Expert Comment

ID: 41843553
select * from
(select t.*,sum(earnedcredits)  over(partition by student_id) creditsum from your_table)
where creditsum >= 5
0

LVL 77

Expert Comment

ID: 41843556
OH, now that I see sdstuber's post, I see where the sum comes in.

Ignore my post...
0

LVL 77

Assisted Solution

slightwv (䄆 Netminder) earned 1000 total points
ID: 41843575
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

LVL 35

Accepted Solution

johnsone earned 1000 total points
ID: 41843598
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

LVL 77

Expert Comment

ID: 41843602
*sigh*  yes...  I diverted from my original thought and copied someone else's syntax...
0

Author Closing Comment

ID: 41843682
Worked great, thanks!
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month7 days, 19 hours left to enroll

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.