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.