title: Sum data by week in Oracle SQL
body: <p>I have an Oracle table (Oracle 11g) that contains hours of overtime worked (WRK_HR) and the date worked (CHRG_DT). I want to build a stored procedure that returns the sum of WRK_HR for Saturday through Friday, each Friday, from the start of the project to the date provided. Say my table contains the following:</p>
  
  <pre><code>CHRG_DT   WRK_HR
  --------  ------
  01/01/14       4
  01/02/14       8
  01/15/14       7
  </code></pre>
  
  <p>My output should look like this (each date is a Friday):</p>
  
  <pre><code>CHRG_DT   TOTAL
  --------  -----
  01/03/14     12
  01/10/14     12
  01/17/14     19
  </code></pre>
  
  <p>Here is the SQL I have:</p>
  
  <pre><code>PROCEDURE GET_OVERTIME(  
    p_chrg_dt IN VARCHAR2,  
    cur OUT sys_refcursor  
  )  
  IS  
  BEGIN  
  OPEN cur FOR  
    SELECT  
    TO_CHAR(CHRG_DT, 'mm/dd/yyyy') AS CHRG_DT,  
    SUM(WRK_HR) AS TOTAL  
    FROM OVERTIME
    WHERE TRUNC(CHRG_DT) &lt;= TO_DATE(p_chrg_dt, 'mm/dd/yyyy')
    GROUP BY CHRG_DT
    ORDER BY CHRG_DT;
  END; 
  </code></pre>
  
  <p>Here is the result from my SQL. As you can see, it gives me a result for each unique day. I need it to total on every given Friday, even if there was no entry that Friday:</p>
  
  <pre><code>CHRG_DT   TOTAL
  --------  -----
  01/01/14      4
  01/02/14     12
  01/15/14     19
  </code></pre>