Assistance creating SQL query in Oracle to split by weeks

Question

I am building a report to show order statistics by week within our company.

Here is the SQL query that I've created:

Select Sum(ORDER_TOTALS.ORDER_TOT_NET) As "ORDER_TOT_NET",
 Sum(ORDER_TOTALS.ORDER_TOT_WGHT) As "ORDER_TOT_WGHT",
 Sum(ORDER_TOTALS.ORDER_TOT_QTY) As "ORDER_TOT_QTY", 
 Sum(ORDER_TOTALS.ORDER_TOT_SQM) As "ORDER_TOT_SQM"
From ORDER ORDER,
 ORDER_TOTALS ORDER_TOTALS
Where ORDER.ORDER_NO = ORDER_TOTALS.ORDER_NO
And ORDER.DATE Between '14-DEC-15' And '18-DEC-15

As you can see this will show results for orders that were created last week however I do not understand now how to make it more advanced so that we can get each week of the current year for example.

Here are the current results:

ORDER_TOT_NET | ORDER_TOT_WGHT | ORDER_TOT_QTY | ORDER_TOT_SQM
1020304050.00 | 4567890 | 92328493 | 3923948

What I want is something around this idea:

 ORDER_TOT_NET | ORDER_TOT_WGHT | ORDER_TOT_QTY | ORDER_TOT_SQM
Week 1 1020304050.00 | 4567890 | 92328493 | 3923948
Week 2 1020304050.00 | 4567890 | 92328493 | 3923948
Week 3 1020304050.00 | 4567890 | 92328493 | 3923948

How could/should I create it so that I have these results but for Week 1 through to the current week? Is this something that I should build directly in to the SQL query?

I am using DBxtra reporting program.

Answer 1

To get the week number of a date use:

to_char(sysdate, 'WW')

You can group on this:

select to_char(ORDER.DATE, 'WW') as WEEK
 Sum(ORDER_TOTALS.ORDER_TOT_NET) As "ORDER_TOT_NET",
 Sum(ORDER_TOTALS.ORDER_TOT_WGHT) As "ORDER_TOT_WGHT",
 Sum(ORDER_TOTALS.ORDER_TOT_QTY) As "ORDER_TOT_QTY", 
 Sum(ORDER_TOTALS.ORDER_TOT_SQM) As "ORDER_TOT_SQM"
From ORDER ORDER, ORDER_TOTALS ORDER_TOTALS
Where ORDER.ORDER_NO = ORDER_TOTALS.ORDER_NO
group by to_char(ORDER.DATE, 'WW');

Instead of WW you can also use IW. The last one gives the ISO Week. Here the beginning or the end of the year might end up in the week of the previous/next year. Look for it in Wikipedia (https://en.wikipedia.org/wiki/ISO_8601) for more information on this.