Re: Oracle SQL/group by/decode

Nice!

Dan Morgan

Nasir Mohammed wrote:

> 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:35:43 CST