top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

Oracle: How does one count/sum RANGES of data values in a column?

+1 vote
85 views
Oracle: How does one count/sum RANGES of data values in a column?
posted Jun 19, 2015 by Kunal Kapoor

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

0 votes

A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:

select f2,
       sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
       sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
       sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
from   my_table
group  by f2;

For equal size ranges it might be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...). Eg.

select ename "Name", sal "Salary",
       decode( trunc(f2/1000, 0), 0, 0.0,
                                  1, 0.1,
                                  2, 0.2,
                                  3, 0.31) "Tax rate"
from   my_table;

strong text

answer Jun 22, 2015 by Manikandan J
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...