OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Trunc Date (Oracle SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Trunc Date [message #633458]

Thu, 19 February 2015 05:11

naga78
Messages: 1
Registered: February 2015

Junior Member

I have a query like this

select trunc(col_date,'hh24'),count(1) from tb1
group by trunc(col_date,'hh24')

I have a situation like for few hours there are 0 records, but the above query gives me records only when there are records present in the table

example

09/02/2015 03:00 1
09/02/2015 05:00 1

What i need is something like this

09/02/2015 0
09/02/2015 01:00 0
09/02/2015 02:00 0
09/02/2015 03:00 1
09/02/2015 04:00 0
09/02/2015 05:00 1

How can we do this ?

Report message to a moderator

Re: Trunc Date [message #633460 is a reply to message #633458]

Thu, 19 February 2015 05:25

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You'll need to "create" missing records. In order to do that, you'll need one of row generator techniques. Give it a try!

Report message to a moderator

Re: Trunc Date [message #633463 is a reply to message #633458]

Thu, 19 February 2015 05:35

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

In the link LF provided, you would need to look at this post http://www.orafaq.com/forum/mv/msg/95011/463394/#msg_463394

[Updated on: Thu, 19 February 2015 05:37]

Report message to a moderator

Previous Topic:	Outer join restricts a condition
Next Topic:	Oracle 11g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 08:06:53 CDT 2024