help creating a hour list with sql.

From: robert salemink <rsalemink_at_upc.nl>
Date: Tue, 18 Jan 2000 14:43:27 +0100
Message-ID: <861qma$sdu$1@news1.xs4all.nl>

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.

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 Received on Tue Jan 18 2000 - 07:43:27 CST