Solved

querying by the sum of a column in decimal

Posted on 2016-10-14
7
85 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
Stressed Out?

Watch some penguins on the livecam!

 
LVL 77

Assisted Solution

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 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