royskatt royskatt - 3 months ago 18
SQL Question

Oracle SQL - Sum and group data by week

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

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      |
+-----+-------+--------+
Comments