Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
1  
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 –  bluefeet Oct 31 '12 at 10:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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