| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: help creating a hour list with sql.
A copy of this was sent to "robert salemink" <rsalemink_at_upc.nl>
(if that email address didn't require changing)
On Tue, 18 Jan 2000 14:43:27 +0100, you wrote:
>I have a problem with creating a select statement that must display a a list
>of hours and a count how many times they are in the table.
>
Here is one method. We create a virtual table with 24 rows (1-24). We create another virtual table with all of the DATES (no time). We cartesian product these -- we end up with a virtual table that has 24 rows per date (each day has a row for each hour). We can then OUTER join to the original table and count.
If you need to do this for just one day -- we can simplify (we don't need to cartesian product the first time -- we just need to outer join the 24 rows to the original table for the given day)
ops$tkyte_at_8.0> create table t ( time date ); Table created.
ops$tkyte_at_8.0> insert into t select created from all_users where rownum < 40
2 /
39 rows created.
ops$tkyte_at_8.0> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; Session altered.
ops$tkyte_at_8.0> select to_char(time, 'mm/dd/yy hh24'), count(*)
2 from t
3 group by to_char(time, 'mm/dd/yy hh24')
4 /
TO_CHAR(TIM COUNT(*)
----------- ----------
08/20/99 16 4 08/20/99 18 17 08/21/99 10 18
ops$tkyte_at_8.0>
ops$tkyte_at_8.0> select t1.day, t1.hour, count(t2.time)
2 from ( select a.day, b.hour
3 from ( select distinct trunc(time) day from t ) a, 4 ( select rownum hour from all_objects where rownum < 25 ) b ) T1, 5 t T2 6 where t1.day = trunc(t2.time(+)) 7 and t1.hour = to_number(to_char(t2.time(+),'hh24'))8 group by t1.day, t1.hour
DAY HOUR COUNT(T2.TIME) -------------------- ---------- -------------- 20-aug-1999 00:00:00 1 0 20-aug-1999 00:00:00 2 0 20-aug-1999 00:00:00 3 0 20-aug-1999 00:00:00 4 0 20-aug-1999 00:00:00 5 0 20-aug-1999 00:00:00 6 0 20-aug-1999 00:00:00 7 0 20-aug-1999 00:00:00 8 0 20-aug-1999 00:00:00 9 0 20-aug-1999 00:00:00 10 0 20-aug-1999 00:00:00 11 0 20-aug-1999 00:00:00 12 0 20-aug-1999 00:00:00 13 0 20-aug-1999 00:00:00 14 0 20-aug-1999 00:00:00 15 0 20-aug-1999 00:00:00 16 4 20-aug-1999 00:00:00 17 0 20-aug-1999 00:00:00 18 17 20-aug-1999 00:00:00 19 0 20-aug-1999 00:00:00 20 0 20-aug-1999 00:00:00 21 0 20-aug-1999 00:00:00 22 0 20-aug-1999 00:00:00 23 0 20-aug-1999 00:00:00 24 0 21-aug-1999 00:00:00 1 0 21-aug-1999 00:00:00 2 0 21-aug-1999 00:00:00 3 0 21-aug-1999 00:00:00 4 0 21-aug-1999 00:00:00 5 0 21-aug-1999 00:00:00 6 0 21-aug-1999 00:00:00 7 0 21-aug-1999 00:00:00 8 0 21-aug-1999 00:00:00 9 0 21-aug-1999 00:00:00 10 18 21-aug-1999 00:00:00 11 0 21-aug-1999 00:00:00 12 0 21-aug-1999 00:00:00 13 0 21-aug-1999 00:00:00 14 0 21-aug-1999 00:00:00 15 0 21-aug-1999 00:00:00 16 0 21-aug-1999 00:00:00 17 0 21-aug-1999 00:00:00 18 0 21-aug-1999 00:00:00 19 0 21-aug-1999 00:00:00 20 0 21-aug-1999 00:00:00 21 0 21-aug-1999 00:00:00 22 0 21-aug-1999 00:00:00 23 0 21-aug-1999 00:00:00 24 0
48 rows selected.
>Table (hourtbl)
>-----------------------------
>Name varchar(20)
>Time Date
>
>Name Time
>------------------------
>sca 01/01/1999 2:22:30
>dfe 01/01/1999 8:24:12
>dfd 01/01/1999 2:23:11
>fdd 01/01/1999 3:12:32
>ggs 01/01/1999 8:34:45
>
>
>prefered Result :
>
>Time count
>--------------------------
>01/01/1999 1 0
>01/01/1999 2 2
>01/01/1999 3 1
>01/01/1999 4 0
>01/01/1999 5 0
>01/01/1999 6 0
>01/01/1999 7 0
>01/01/1999 8 2
>
>
>What I have until now:
>
>select count(*),
> TO_DATE(hourtbl,'MM/DD/YY HH24)
> from v$loghist
>group by TO_DATE(hourtbl,'MM/DD/YY HH24)
> /
>
>Result
>
>Time count
>--------------------------
>01/01/1999 2 2
>01/01/1999 3 1
>01/01/1999 8 2
>
>Now I must place the hours that have 0 transactions.
>
>Please help
>
>
>
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 18 2000 - 08:35:29 CST
 |
 |