Re: Histogram/Bucketizing resultset

No reason not to have a database in front of you - the enterprise evaluation edition of SQL Server is free and lasts for 120 days...

http://www.microsoft.com/sql/evaluation/trial/

Anyway, one way or another, if you find a better solution to the bucketing, let us know.

SK

Mark Townsend wrote:

> in article B9E741D5.C3D%markbtownsend_at_attbi.com, Mark Townsend at
> markbtownsend_at_attbi.com wrote on 10/31/02 8:24 PM:
>
> > Hence the WIDTH_BUCKET function - something like (I don't have Oracle at
> > home to test this) -
> >
> > SELECT WIDTH_BUCKET(age, lbound, ubound, nbr_buckets) "Bucket Nbr", count(*)
> > FROM t
> > GROUP BY "Bucket Nbr";
> >
> > where lbound is min(age), ubound is max(age), and nbr_buckets is the number
> > of buckets you want (10)
> >
> > Note that Bucket Nbr would be a number i.e 1 for the first bucket, 2 for the
> > second bucket etc.
> >
> > To turn this number into the actual range statement, you would need
> > something like an inline view
> >
> > SELECT lbound+(((ubound-lboundb)/nbr_buckets) * (b.bucket_nbr-1))||' - '||
> > lbound+(((ubound-lboundb)/nbr_buckets) * b.bucket_nbr) "Bucket",
> > val "Count"
> > FROM (SELECT WIDTH_BUCKET(age, lbound, ubound, nbr_buckets) "Bucket_Nbr",
> > count(*) val
> > FROM t
> > GROUP BY "Bucket") b
> >
> > Note that this will work (assuming the theory is correct) for any range of
> > values, but more importantly for any number of buckets. YMMV, as there is
> > bound to be some debugging required for the above pseudo code.
>
> Apologies - I just realized I missed the most significant part of your
> follow up post
>
> >> Assume that the lower bound and the upper bound is not preset
>
> I'll need to think about this a bit more, at the office, with a database in
> front of me.
Received on Fri Nov 01 2002 - 22:21:15 CST