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.

  • 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. – FrustratedWithFormsDesigner Oct 30 '12 at 20:52

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

  • 2
    I'm guessing the "created" dates also have a time part, and some form of truncing would help the OP. – Mat Oct 31 '12 at 6:52
  • @Mat good point, updated with query that includes trunc – Taryn Oct 31 '12 at 10:00
  • Nice solution! Didnt realize there is a trunc function for Oracle. – Thyag Aug 29 '19 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.