Re: Histogram/Bucketizing resultset

Here's an idea for you, written for SQL Server.

If you are using SQL Server pre-2000, change the table variable to a temporary table - if you're using Oracle, you'll have to figure out the changes on your own, though it shouldn't be all that hard.

Steve Kass
Drew University

NoSpamPlease wrote:

> Thx Mark
> My bad - I framed the question incorrectly
>
> Assume that the lower bound and the upper bound is not preset
> I need to do this in 1 single query
> Hence
> LB = minimum of the attribute in the table
> UB = max of the attribute in the table
> Bucketsize = UB-LB/10 <- 10 is -need 10 buckets
> Age
> > > 27
> > > 24
> > > 34
> > > 56
> > > 40
> > > 76
> > > 30
> > > 32
> > > 67
> > > 71
>
> LB = 24
> UB = 76
> BucketSize =(76 -24)/10
>
> Bucket Count
> 24-29.2 2
> (total 10 rows)
> ..
> 70.8-76 1
>
> Mark Townsend <markbtownsend_at_attbi.com> wrote in message news:<B9E5FECF.B3D%markbtownsend_at_attbi.com>...
> > in article 499d4ffb.0210302045.452ef143_at_posting.google.com, NoSpamPlease at
> > googleposer_at_yahoo.com wrote on 10/30/02 8:45 PM:
> >
> > > Given an input table T(age)
> > >
> > > Age
> > > 27
> > > 24
> > > 34
> > > 56
> > > 40
> > > 76
> > > 30
> > > 32
> > > 67
> > > 71
> > >
> > > I would like to generate the following output
> > >
> > > Range Value
> > > 0-10 0
> > > 11-20 0
> > > 21-30 2
> > > 31-40 4
> > > 41-50 0
> > > 51-60 1
> > > 61-70 1
> > > 71-80 2
> > > 81-90 0
> > > 91-100 0
> > >
> > >
> > > Can anyone help me with a query
> > > Thx in adv
> >
> > For an Oracle solution, assuming the bucket ranges you have given, something
> > like
> >
> > SELECT
> > (CASE WHEN age BETWEEN 0 AND 10 THEN ' 0 - 10'
> > WHEN age BETWEEN 11 AND 20 THEN '11 - 20'
> > WHEN age BETWEEN 21 AND 30 THEN '21 - 30'
> > ...
> > END)
> > AS Range,
> > COUNT(*) AS Value
> > FROM T
> > GROUP BY
> > (CASE WHEN age BETWEEN 0 AND 10 THEN ' 0 - 10'
> > WHEN age BETWEEN 11 AND 20 THEN '11 - 20'
> > WHEN age BETWEEN 21 AND 30 THEN '21 - 30'
> > ...
> > END);
> >
> > This obviously becomes a PITA with more buckets.
> >
> > Depending on your actual requirements, you may also want to investigate
> > NTILE or WIDTH_BUCKET functions, which will automatically create either
> > equiwidth buckets or histograms for you, respectively -
> > http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920
> > /a96520/analysis.htm
Received on Fri Nov 01 2002 - 18:22:18 CST