We help IT Professionals succeed at work.

Tough SQL Query question

IsleOfView
IsleOfView asked
on
Medium Priority
2,438 Views
Last Modified: 2007-12-19
Tough to me at least... :)

I have a table that collects orders.  There is a field for a date, which is populated automatically via a trigger on row creation using sysdate (so it has the exact system time in it).

I need to create a query which will give me the total number of orders per day--e.g.:

DATE      | COUNT
------------------
18-SEP-01 | 3523
19-SEP-01 | 5923

Any idea on how to accomplish this?
Comment
Watch Question

Commented:
select trunc(date), count(*) fro m order_table
group by trunc(date);

Commented:
try one of these

give total for all days
   SELECT date, count()*
   FROM my_table
   GROUP BY date;

give total for yesterday
   SELECT date, count()*
   FROM my_table
   WHERE date > sysdate -1
   GROUP BY date;



Commented:
Use the trunc(date) function.

select trunc(date), count(*) from table
group by trunc(date)
order by trunc(date);

The trunc function works well with the date column. By default it rounds off to the day, if you want to have a count of orders by hour, you can do that with the following command.

select trunc(date, 'HH'), count(*) from table
group by trunc(date, 'HH')
order by trunc(date, 'HH');

where HH is the hour format.

Author

Commented:
hareshkc ---- perfect.  Thanks a lot!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.