Re: Oracle SQL/group by/decode

From: Nasir Mohammed <nasir_mohd_at_hotmail.com>
Date: Wed, 20 Feb 2002 17:18:18 -0800
Message-ID: <1014254567.385475@sj-nntpcache-3>

I had a similar requirement and following is the code for that. May be it could give you some pointers.

select decode(sign(153500000 - erptrans_id),-1,

    decode(sign(154000000 - erptrans_id),-1,

      decode(sign(154500000 - erptrans_id),-1,
        decode(sign(155000000 - erptrans_id),-1,'> 155000000',
          'Between 154500000 and 155000000'),
         'Between 154000000 and 154500000'),
        'Between 153500000 and 154000000'),
       '<= 153500000') Range,

  count(*)
from tablea
group by decode(sign(153500000 - erptrans_id),-1,

    decode(sign(154000000 - erptrans_id),-1,

      decode(sign(154500000 - erptrans_id),-1,
        decode(sign(155000000 - erptrans_id),-1,'> 155000000',
          'Between 154500000 and 155000000'),
         'Between 154000000 and 154500000'),
        'Between 153500000 and 154000000'),
       '<= 153500000')

"David Howland" <dhowland123_at_home.com> wrote in message news:KLVc8.52088$p5.8215151_at_news1.rdc1.nj.home.com...
> Hi all:
> I am trying to write an SQL in Oracle and could use some help. I have a
> table
> that has 2 columns (lets say), col1 is a timestamp and column 2 is an id.
> Using
> a join to another table I can extract a count of the id's I want and want
to
> group by
> a range of times. For example:
>
> time id
> 01:25 1
> 01:45 2
> 01:55 3
> 02:25 4
> 03:15 5
> 04:25 6
> 05:35 7
> 06:45 8
> 07:00 9
> 08:15 10
> 09:25 11
>
>
>
>
> Okay, so in conclusion I need an SQL that will get a count of the number
of
> id's by
> a range and return something like
>
> time occurrences
>
> 01:00 - 02:00 3
> 02:00 - 03:00 1
> 03:00 - 04:00 1
> 04:00 - 05:00 1
>
>
> etc....
>
>
> I have tried Oracle's decode and it doesn't seem that I can test ranges of
> data in the condition and I can't find examples of 'case' anywhere, nor
get
> it to work. Group doesn't appear to allow this kind of functionality.
> Any thoughts?
> thanks, Dave
>
>
>
Received on Wed Feb 20 2002 - 19:18:18 CST