25

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

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
royskatt
  • 1,190
  • 2
  • 15
  • 35

4 Answers4

41

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.

ntalbs
  • 28,700
  • 8
  • 66
  • 83
  • 5
    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. – Andrejs Jun 21 '16 at 09:42
  • @Andrejs 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? – M. Justin Feb 16 '23 at 13:56
37

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      |
+-----+-------+--------+
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • 1
    Works nicely! Modified it to SELECT to_char(DATE - 7/24,'YYYYIW'), SUM(AMOUNT) FROM YourTable GROUP BY to_char(DATE - 7/24,'YYYYIW') – royskatt Aug 12 '14 at 09:10
  • 7
    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. – Andrejs Jun 21 '16 at 09:27
0

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
Manoj Kumar
  • 5,273
  • 1
  • 26
  • 33
  • 2
    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)? – TastySlowCooker Feb 04 '20 at 05:33
0

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

 GROUP BY to_char(createddate, 'IYYY IW')

Example output:

2022 46 2023 01

Mark
  • 21
  • 4