How do you aggregate values into predefined ranges?

From: ramdan <ramdan_at_mailexcite.com>
Date: 2000/04/01
Message-ID: <xbrF4.11740$ua.134340@newscene.newscene.com>#1/1

We have an sql query that returns bill account number, name, location, tariff, and energy usage. usage ranges from 1 to 10,000

i.e. B0124322 john smith loc001 tarif001 234

       B0124666  ian davis    loc001  tarif001  678
       B0568433  julio some  loc001  tarif001  210
       B4654332 mary sue    loc002  tari004   345

we need to create a report where data is grouped by location and tariff. Then we have specific energy ranges(i.e. 0-99) and we need to add the each range the number of bills where the energy was in that range (each bill is count as 1)

i.e.

                        loc001
                                tariff01

usage-range

0-99                            0

100-199                       0  

200-299                        2

300-500                         0

500-700                         1 


                        loc002
                                tariff04

usage-range

0-99                            0

100-199                       0  

200-299                        0

300-500                         1

500-700                         0 

grouping loc and tariff is no problem

But how do we make 234 fall into the 200-299 range. We have to do this to over 50 million recs so we are looking for a very efficient way perfer SQL but pl/sql ok(unless it much faster)

Any ideas? Received on Sat Apr 01 2000 - 00:00:00 CST