Home » SQL & PL/SQL » SQL & PL/SQL » group by date range (oracle 10 g)
group by date range [message #413615] Thu, 16 July 2009 13:36 Go to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Hi Team,
I am looking for a query which sum by values based on the group by date range. here is my requirement.

DDL for table.
create table date_rang_tab
(date_col date,
val1 number,
val2 number,
val3 number);

data in the table looks like below.
DATE_COL VAL1 VAL2 VAL3
1/1/2009 10 11 12
1/2/2009 11 12 13
1/3/2009 12 13 14
1/4/2009 13 14 15
1/5/2009 14 15 16
1/6/2009 15 16 17

i want to group by date_col, by grouping every 2 days and sum the column values of val1, val2 and val3 individually. I want the output in the below format.

group by dates jan1 and jan2 | sum of val1 for jan1 and jan2| sum of val2 for jan1 and jan2..like that.
in above | symbol is just a decimeter i have used to separate the values.

1/1/2009-1/2/2009 21 23 25
1/3/2009-1/4/2009 25 27 29
1/5/2009-1/6/2009 29 31 33

Thanks in advance for your help
Re: group by date range [message #413616 is a reply to message #413615] Thu, 16 July 2009 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous topics:
Michel Cadot wrote on Mon, 23 March 2009 10:13
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
...

Post a working Test case: create table and insert statements along with the result you want with these data.

Clue: group by trunc(julian date/2).

Regards
Michel

Re: group by date range [message #413684 is a reply to message #413615] Fri, 17 July 2009 01:00 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
LEAD() and ROW_NUMBER() will complete your task.

regards,
Delna
Re: group by date range [message #413688 is a reply to message #413684] Fri, 17 July 2009 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, just a group by as the question says it:
Quote:
i want to group by date_col, by grouping every 2 days and sum the column values of val1, val2 and val3


Regards
Michel
Re: group by date range [message #413745 is a reply to message #413688] Fri, 17 July 2009 08:04 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Dear All Experts,
please help me on this by providing feasible solutions.
Appreciate for spending your valuable time for me.

Regards
Murali
Re: group by date range [message #413746 is a reply to message #413745] Fri, 17 July 2009 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post what you tried based on the clue we gave.

Regards
Michel

[Updated on: Fri, 17 July 2009 08:11]

Report message to a moderator

Re: group by date range [message #413747 is a reply to message #413616] Fri, 17 July 2009 08:14 Go to previous messageGo to next message
cookiemonster
Messages: 12421
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Thu, 16 July 2009 19:42

Clue: group by trunc(julian date/2).


Re: group by date range [message #413749 is a reply to message #413747] Fri, 17 July 2009 08:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, if you want the other set of groups:
Clue: group by trunc((julian date+1)/2).


Extra hint:
TO_NUMBER(TO_CHAR(<date>,'J'))
Previous Topic: getting last modified table name
Next Topic: Compliation errors and Please Help with Nested Table merged (many)
Goto Forum:
  


Current Time: Thu Dec 08 08:40:53 CST 2016

Total time taken to generate the page: 0.11886 seconds