Re: Histogram/Bucketizing resultset

From: John Gilson <jag_at_acm.org>
Date: Thu, 31 Oct 2002 14:21:58 GMT
Message-ID: <a0bw9.63817$gB.14542866@twister.nyc.rr.com>

"NoSpamPlease" <googleposer_at_yahoo.com> wrote in message news:499d4ffb.0210302045.452ef143_at_posting.google.com...
> 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

SELECT lower, upper, COALESCE(COUNT(age), 0) FROM (SELECT 0, 10

             UNION ALL
             SELECT 11, 20
             UNION ALL
             SELECT 21, 30
             UNION ALL
             SELECT 31, 40
             UNION ALL
             SELECT 41, 50
             UNION ALL
             SELECT 51, 60
             UNION ALL
             SELECT 61, 70
             UNION ALL
             SELECT 71, 80
             UNION ALL
             SELECT 81, 90
             UNION ALL
             SELECT 91, 100) AS Buckets(lower, upper)
            LEFT OUTER JOIN
            T
            ON age BETWEEN lower AND upper

GROUP BY lower, upper
ORDER BY lower ASC

Regards,
jag Received on Thu Oct 31 2002 - 08:21:58 CST