counting values within a range
cdkDec 8 2005 — edited Dec 8 2005given a table called SALARY, with one relevant column called PERCENT_INCREASE containing a set of data such as:
0.00
0.00
1.79
2.25
2.38
2.49
2.52
2.56
2.62
2.63
2.63
2.69
2.8
2.84
2.85
2.86
2.9
2.96
3.01
3.35
3.36
3.38
3.38
3.39
3.42
3.45
3.5
3.5
4.01
4.04
4.12
4.17
5.15
how can i get a count broken down into 1% groups, like so
PERCENT . .COUNT
0. . . . . . . . . 2
0 - 1. . . . . . .0
1 - 2. . . . . . .1
2 - 3. . . . . . .15
3 - 4. . . . . . .10
4 - 5. . . . . . .4
5 - 6. . . . . . .1
(ignore the periods, they're just to make the column values line up in this posting)
i've accomplished this, but the query i came up with is disgusting and virtually impossible to allow for the interval to be specified by the user, like if they wanted to see the data in 2% intervals instead.
this is what i'm using currently to show the data in 0.5% groupings:
select ltrim(decode(step,
0,
step,
to_char(ltrim(step) - 0.4, 990.9) || ' - ' ||
ltrim(step))) step,
total
from (
select to_char(ceil((percent_increase - 0.09) / 0.5) * 0.5, 990.9) step,
count(*) total
from salary
group by to_char(ceil((percent_increase - 0.09) / 0.5) * 0.5, 990.9))
)
there has got to be cleaner way to do this. will analytical functions do anything for me here? any suggestions?