Home » SQL & PL/SQL » SQL & PL/SQL » Count grouped by 6month period (9i)
Count grouped by 6month period [message #287740] Thu, 13 December 2007 06:01 Go to next message
noon
Messages: 2
Registered: December 2007
Junior Member
My original thought was to have a cursor, pass in a parameter, display the count returned, increment the parameter, and repeat.

But I can't seem to get this to work nor do I think that it is the best solution. I have a table with several million rows and 9i faces rollback segment errors on large deletes. I want to get an idea of how many transactions occurred in 6month steps from a predetermined start to end date.

If anyone could help, much appreciated.
Re: Count grouped by 6month period [message #287753 is a reply to message #287740] Thu, 13 December 2007 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from mytable where mydate between the dates you want.

What can we say more with the stuff you posted.
You talked about delete, are they anything to do with your question?
By the way what is the question?
What is the table definition?
What is the input? To what field it referes?
...

Regards
Michel

Re: Count grouped by 6month period [message #287765 is a reply to message #287753] Thu, 13 December 2007 07:01 Go to previous messageGo to next message
noon
Messages: 2
Registered: December 2007
Junior Member
The situation is we are deleting about 60million transactions because they are more than 2 years old. Dates begin in the 2000's. I know that I can select * between a date range. But I want the counts to be broken down per 6 month period and don't really want to hard code date1 to date2, date3 to date4, date5 to date6...and select from each range. Is there a way to group by date, but instead of grouping per day, grouping per 6month period?

Deletion will come later in the process. Now I just want to know what kind of size selects I want to deal with.
Re: Count grouped by 6month period [message #287766 is a reply to message #287765] Thu, 13 December 2007 07:03 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Is there a way to group by date, but instead of grouping per day, grouping per 6month period?

Yes.

Regards
Michel
Previous Topic: no record fetched
Next Topic: How to find the right partition or its existence to a value
Goto Forum:
  


Current Time: Sun Dec 04 06:42:14 CST 2016

Total time taken to generate the page: 0.09451 seconds