Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to group values according to the weeks of the year ?

christian41936Nov 6 2008 — edited Nov 17 2008
Hi,

I have a table with dates and values (I simplify for the need of this question)

I want to group the values (and summing them) according to the 52 weeks of a year. I mean grouping week for week.
Here are the date of each weeks generated for one year, it gives all the mondays of each week of the year :
select trunc(trunc(sysdate,'year'),'iw')+(rownum*7)-7 n from all_objects where rownum <= 53

What I want to do is to sum up the values of my table and group them into each week they pertain to. These weeks date are generated by the query above.

For example all the values falling into the 1st week of the year will be grouped into this date, all the values falling into the 2nd wek will be grouped into this date ans so on. If no values are found for a week, then a 0 value is generated, in iorder to have a 52 weeks-lines. It is basically a kind of select with "date between .... and ..." and grouping the values and affecting them to the number of the wee they do pertain.

Examples of mondays 30/06/2008, 07/07/2008, 14/07/2008

Example of values and dates into my table :

Datte Value
30/06/08 9
01/07/08 9
02/07/08 9
03/07/08 9
03/07/08 9
04/07/08 6
04/07/08 6
04/07/08 9
07/07/08 9
08/07/08 9
29/07/08 9
29/07/08 6
30/07/08 9
31/07/08 9
31/07/08 9
31/07/08 9
01/08/08 9
04/08/08 5,5
04/08/08 9
07/08/08 9
07/08/08 9
08/08/08 9
14/08/08 9
14/08/08 9
18/08/08 9
22/08/08 9
22/08/08 9
25/08/08 9
25/08/08 9
26/08/08 9
29/08/08 9
29/08/08 9
01/09/08 9
01/09/08 9
04/09/08 9
04/09/08 9
05/09/08 9
05/09/08 9
05/09/08 9
05/09/08 9
17/09/08 9

Result should be something like this :

Number of the week Date Value
1 31/12/2008 47
2 07/12/2008 94
3 14/01/2008 23
and so on....
.....
52 22/12/2008 73
53 29/12/2008 60

Hope I am clear. Thank you for your kind answers.
This post has been answered by Boneist on Nov 13 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 15 2008
Added on Nov 6 2008
19 comments
1,093 views