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?`

Idolum

Answer 1

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

Graecoroman

Works nicely! Modified it to SELECT to_char(DATE - 7/24,'YYYYIW'), SUM(AMOUNT) FROM YourTable GROUP BY to_char(DATE - 7/24,'YYYYIW') – Idolum

I don't understand why the "-7/24". Why substract 7 hours? What is the benefit of this? In fact, subtracting 7 hours from Monday morning could push the date to Sunday and as such show the previous week, which would be wrong. – Niggle

Answer 2

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.

Histiocyte

I suggest you mention that in this scenario it's important to trunc(date,'IYYY') and group by it as well (similar to Vignesh's answer), otherwise the same weeks from different years will be grouped into one. – Niggle

@Niggle I'm confused by your assertion that grouping by the week needs to be combined with grouping by the year. trunc(date, 'IW') appears to return a full date such as "2023-12-25". Shouldn't grouping by that be sufficient to group by a week in a particular year? – Shavers

Answer 3

I guess this would help as well....

 /* Weekly sum of values */
 SELECT SUM( Amount ) as Sum_Amt, 
 DATEPART (wk, Date) as WeekNum
 FROM databse_name.table_name
 GROUP BY DATEPART (wk, Date)
 ORDER BY WeekNum

 /* Monthly sum of values */
 SELECT SUM( Amount ) as Sum_Amt, 
 DATEPART (mm, Date) as MonNum
 FROM databse_name.table_name
 GROUP BY DATEPART (mm, Date)
 ORDER BY MonNum

Electromyography

The question is specifically about Oracle SQL (pl/sql) which doesn't have a function DATEPART. I assume your code is for MS SQL Server (tsql)? – Unmeasured

Answer 4

For Oracle SQL to group by year and week number, use:

 GROUP BY to_char(createddate, 'IYYY IW')

Example output:

2022 46 2023 01

Sitarski