Need to count records and group count by date on Oracle

Question

I have a table like the following

 ID created sent type
-----------------------------------------------------
0001463583000051783 31-JUL-12 1 270
0081289563000051788 01-AUG-12 1 270
0081289563000051792 01-AUG-12 1 270
0081289563000051791 01-AUG-12 1 270
0081289563000051806 01-AUG-12 1 270
0001421999000051824 06-AUG-12 1 270
0001421999000051826 06-AUG-12 1 270
0001464485000051828 06-AUG-12 1 270
0082162128000051862 09-AUG-12 2 278
0082162128000051861 09-AUG-12 2 278
0022409222082910259 09-AUG-12 3 278

I would like to have the following for output

created Count
---------------------
31-JUL-12 1
01-AUG-12 4
06-AUG-12 3
09-AUG-12 3

How hard would it be to accomplish this using SQL Developer on Oracle 10g?

I have tried several queries to generate such a table and in the end it does not group the count by date, just gives me a '1' for the count when we average 5000-10000 transactions daily. I'm probably overcomplicating it. But I would like something simple where I can pull the amount of transactions on a daily basis within a date range.

• FrustratedWithFormsDesigner: How about you post the queries you've tried that didn't work? I wouldn't want to suggest a query only for you to reply that you've already tried it.

Answer 1

Unless I am missing something, your query would be something like this:

select created, count(*) CreatedCount
from yourtable
group by created
order by created;

See SQL Fiddle with Demo

Or if you have a time associated with the date, you can use TRUNC:

select trunc(created), count(*) CreatedCount
from yourtable
group by trunc(created)
order by trunc(created);

See SQL Fiddle with Demo

• Mat: I'm guessing the "created" dates also have a time part, and some form of truncing would help the OP.
• Taryn: @Mat good point, updated with query that includes trunc
• Thyag: Nice solution! Didnt realize there is a trunc function for Oracle.