WIDTH_BUCKET

Syntax:

WIDTH_BUCKET(column-name/expr, low/min,high/max,bucket_count) ;

Divide a set o data into buckets (ie: partition ) with an equal interval.

eg : Number of Cars 0-10 , 10-20 ,30-40 ,40-50

If we specify n buckets then n+2 buckets are generated .The extra 2 buckets are value above the high/max boundary and below the low/min boundary.

select WIDTH_BUCKET(sal,1000,10000,3) util ,sal from (select 5700 sal from dual
union
select 500 sal from dual
union
select 1000 sal from dual
union
select 6000 sal from dual
union
select 9000 sal from dual
union
select 90000 sal from dual
union
select 4500 sal from dual)


Instead of Width_bucket Case Function can be used .But here we want to manually divide the range there may or may not be equal interval its all up to the SQL programmer .


Syntax:

CASE WHEN THEN
WHEN THEN
[ELSE Value n ] END


SELECT SUM(CASE WHEN SUM(amount) BETWEEN 0 AND 49 THEN 1
ELSE 0 END) AS "0-49", SUM(CASE WHEN SUM(amount) BETWEEN 50 AND 100
THEN 1 ELSE 0 END) AS "50-100"  FROM sales

-----------