Home » SQL & PL/SQL » SQL & PL/SQL » Query with Timestamp and aggregate Function (oracle 10g)
Query with Timestamp and aggregate Function [message #361960] Fri, 28 November 2008 15:37 Go to next message
bandsb
Messages: 1
Registered: November 2008
Junior Member
I want to get the users who logged in each hour of the day

In my table i have the column called created_at, there i am storing timestamp,

I need a query to retrieve the data like

Time count

1-2 44
2-3 35
3-4 126
upto
23-24 410

How can i get the data in this format using SQL Query .

I would appreciate if any one help me out , i am newbie to Oracle

[Updated on: Fri, 28 November 2008 23:40]

Report message to a moderator

Re: Query with Timestamp and aggregate Function [message #361973 is a reply to message #361960] Sat, 29 November 2008 00:20 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@bandsb,

Please go through OraFAQ Forum Guide especially on "How to Format Your Post?"

Post a test case and always explain your desired results in words.

As for your query, extract the Hour from your column - CREATED_AT and group it based on that.(I have guessed that you want to count irrespective of the date) Hope the following links help to understand the concepts:

GROUP BY Clause
TO_CHAR Function
COUNT Function
EXTRACT Function

Regards,
Jo

[Updated on: Sat, 29 November 2008 00:23]

Report message to a moderator

Re: Query with Timestamp and aggregate Function [message #361976 is a reply to message #361960] Sat, 29 November 2008 00:25 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I prefer the use of EXTRACT in this case.

Regards
Michel
Previous Topic: Analytical Function Sum(distinct value)
Next Topic: Calculation Field and JavaScript and PL/SQL (2 threads merged by bb)
Goto Forum:
  


Current Time: Sun Dec 04 04:31:13 CST 2016

Total time taken to generate the page: 0.04119 seconds