Oracle SQL - Sum and group data by week

Question

I have records related to dates:

DATE         AMOUNT 16.03.2013   3 16.03.2013   4 16.03.2013   1 16.03.2013   3 17.03.2013   4 17.03.2014   3 

I know how to sum them up for each day, but how could I sum them up by week?`

Answer 1

You can use TRUNC function to truncate date to the first day of week. There are a few ways of defining week. For example, if you want to treat that the first day of week is Monday, you can IW format, like this:

select trunc(date, 'IW') week, sum(amount) from YourTable group by trunc(date, 'IW'); 

You can also TO_CHAR function as the "@Vignesh Kumer"'s answer.

The point is that you should truncate the date in the same week into one value. Then group by the value. That's it.

Answer 2

Try this

SELECT to_char(DATE - 7/24,'IYYY'), to_char(DATE - 7/24,'IW'),SUM(AMOUNT) FROM YourTable GROUP BY to_char(DATE - 7/24,'IYYY'), to_char(DATE - 7/24,'IW') 

FIDDLE DEMO

---

Output would be:

+-----+-------+--------+ |YEAR | WEEK  | AMOUNT | +-----+-------+--------+ |2013 | 11    | 18     | |2013 | 13    | 3      | +-----+-------+--------+