Re: Histogram/Bucketizing resultset

From: Mark Townsend <markbtownsend_at_attbi.com>
Date: Thu, 31 Oct 2002 05:26:06 GMT
Message-ID: <B9E5FECF.B3D%markbtownsend@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 Wed Oct 30 2002 - 23:26:06 CST