I have the following code which groups data by month.

select project_name, to_char (date_sys, 'Month') as Month, sum(BATCH.RECORDS_NUMBER) as Count_of_Documents
 from 
    BATCH
    group by project_name, to_char(date_sys, 'Month');

I am wanting to group by week now as following:

 ------------ ------- 
|week        | Count |
 ------------ ------- 
| 2016-12-05 |    14 |
| 2016-12-12 |    20 |
| 2016-12-19 |    19 |
| 2016-12-26 |    15 |
| 2017-01-02 |    13 |
| 2017-01-09 |    14 |
| 2017-01-16 |    12 |
| 2017-01-23 |    12 |
| 2017-01-30 |     5 |

date_format and datepart do not seem to work with Oracle SQL as I get "ORA-00904: “DATE_FORMAT”: invalid identifier" error. I was wondering if anyone can help with the code. I'd also appreciate if it can be sorted by daily counts. Thank you

CodePudding user response：

I would suggest TRUNC instead of TO_CHAR, because when your data spans over more than a year, then it will group several years into one record.

select project_name, to_char(TRUNC(date_sys, 'MM'), 'Month') as Month,      
   sum(BATCH.RECORDS_NUMBER) as Count_of_Documents
from BATCH
group by project_name, TRUNC(date_sys, 'MM');

Assuming you want to group by week according to ISO-8601 standard use

select project_name, to_char(TRUNC(date_sys, 'IW'), 'YYYY-"W"IW') as WEEK,      
   sum(BATCH.RECORDS_NUMBER) as Count_of_Documents
from BATCH
group by project_name, TRUNC(date_sys, 'IW');

If you like to start your week on Sunday, then you can use

select project_name, TO_CHAR(TRUNC(date_sys 1, 'IW')-1, 'MM/DD/YYYY') as week,       
   sum(BATCH.RECORDS_NUMBER) as Count_of_Documents
from BATCH
group by project_name, TRUNC(date_sys 1, 'IW');

You can also group by starting day of the week, 'D'. Be aware, the output of TRUNC(date_sys, 'D') depends on current user session NLS_TERRITORY settings, so the output might be not consistent.

See ROUND and TRUNC Date Functions

CodePudding user response：

You can get the week number of the year using ISO week or, if you want that week 1 starts on the first day of year, then use the second option below 'ww':

WITH
    dates AS
        (   Select 
                To_Date('01.01.2022', 'dd.mm.yyyy')   (LEVEL - 1)   ((LEVEL - 1)*2) "A_DATE"
            From Dual 
            Connect By LEVEL <= 9   )
Select
    A_DATE,
    To_Char(A_DATE, 'IW') "ISO_WEEK_NO",
    To_Char(A_DATE, 'ww') "WW_WEEK_NO"
From dates

R e s u l t :

Do the Group By using one of the above week numbers like you did it by months.

And if you want Sunday here is how to get the date of Sunday week before today:

SELECT NEXT_DAY (SYSDATE - 4, 'SUNDAY') - 7 "SUNDAY_WEEK_BEFORE" FROM dual
--  
--  SUNDAY_WEEK_BEFORE
--  ------------------
--  11-DEC-22