[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 111
  • Last Modified:

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!!
0
Basssque
Asked:
Basssque
2 Solutions
 
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
 
slightwv (䄆 Netminder) Commented:
OH, now that I see sdstuber's post, I see where the sum comes in.

Ignore my post...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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:
*sigh*  yes...  I diverted from my original thought and copied someone else's syntax...
0
 
BasssqueAuthor Commented:
Worked great, thanks!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now