Oracle SQL Functions

WIDTH_BUCKET

This function lets you create histograms.

This query does first (inner select) assign a bucket to the companies. This will be a number between 0 and 11. 0 is any company which has a minus value (underflow bucket), 1 is a company having a value between 0 and 100, 2 between 101 and 200, … 11 are all companies that have a value of more than 1000 (overflow bucket). The outer select does then group all companies according to their value. At the end we get a result like 5 companies have a value between 0 and 100, 300 companies have a value betwwen 101 and 200 and so on.

select
  bucket, 
  count(bucket)
from (select 
        width_bucket(c.value, 0, 1000, 100) bucket
      from 
        company c)
group by bucket
order by bucket;

See Oracle Documentation about WIDTH_BUCKET

Leave a comment

Your email address will not be published. Required fields are marked *