SQL SERVER 2008 – How does one count/sum RANGES of data values in a column

 

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;

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s