Re: Oracle SQL/group by/decode

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 21 Feb 2002 12:26:01 +0300
Message-ID: <a52e8u$4ll$1@babylon.agtel.net>

A rather trivial way to do this with GROUP BY:

SQL> select to_char(col1,'HH24:MI') time, col2 id from grptest 2 /

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

11 rows selected.

SQL>
SQL> select to_char(trunc(col1,'HH'),'HH24:MI')||'-'||to_char(trunc(col1+1/24,'HH'),'HH24:MI') as range, count(col2) as occurences from grptest 2 group by to_char(trunc(col1,'HH'),'HH24:MI')||'-'||to_char(trunc(col1+1/24,'HH'),'HH24:MI') 3 /

RANGE OCCURENCES

----------- ----------
01:00-02:00          3
02:00-03:00          1
03:00-04:00          1
04:00-05:00          1
05:00-06:00          1
06:00-07:00          1
07:00-08:00          1
08:00-09:00          1
09:00-10:00          1

9 rows selected.

Looks like what you wanted :)

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"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 Thu Feb 21 2002 - 03:26:01 CST