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
7
Medium Priority
?
96 Views
Last Modified: 2016-10-14
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
Comment
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
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
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

by:sdstuber
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

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

Ignore my post...
0
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
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

by:
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

by:slightwv (䄆 Netminder)
ID: 41843602
*sigh*  yes...  I diverted from my original thought and copied someone else's syntax...
0
 

Author Closing Comment

by:Basssque
ID: 41843682
Worked great, thanks!
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

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.

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.

Join & Ask a Question