In article <xbrF4.11740$ua.134340_at_newscene.newscene.com>,
ramdan_at_mailexcite.com (ramdan) wrote:
> 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?
>
Decode might do the trick for you. for example:
ops$tkyte_at_8i> create table t as select rownum usage from all_objects
where rownum <= 750;
Table created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> select decode( trunc(usage/100), 0, '0-99',
2 1, '100-199',
3 2, '200-299',
4 3, '300-499',
5 4, '300-499',
6 5, '500-700',
7 6, '500-700',
8 decode( usage, 700, '500-
700', 'Over 700' ) ),
9 count(*)
10 from t
11 group by
12 decode( trunc(usage/100), 0, '0-99',
13 1, '100-199',
14 2, '200-299',
15 3, '300-499',
16 4, '300-499',
17 5, '500-700',
18 6, '500-700',
19 decode( usage, 700, '500-
700', 'Over 700' ) )
20 /
DECODE(T COUNT(*)
-------- ----------
0-99 99
100-199 100
200-299 100
300-499 200
500-700 201
Over 700 50
6 rows selected.
You could hide the complexity of the decode in a view and group by the
derived column.
--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Apr 01 2000 - 00:00:00 CST
