sql - Weekly total sums

Question

I have a table in a PostgreSQL database containing dates and a total count per day.

mydate       total
2012-05-12      12 
2012-05-14       8 
2012-05-13       4 
2012-05-12      12 
2012-05-15       2 
2012-05-17       1 
2012-05-18       1 
2012-05-21       1 
2012-05-25       1 

Now I need to get the weekly totals for a given date range.
Ex. I want to get the weekly totals from 2012-05-01 up to 2012-05-31.

I'm looking at this output:

2012-05-01 2012-05-07   0
2012-05-08 2012-05-14  36
2012-05-15 2012-05-22   5
2012-05-23 2012-05-29   1
2012-05-30 2012-05-31   0

Answer 1

This works for any given date range:

CREATE FUNCTION f_tbl_weekly_sumtotals(_range_start date, _range_end date)
  RETURNS TABLE (week_start date, week_end date, sum_total bigint)
  LANGUAGE sql AS
$func$
SELECT w.week_start, w.week_end, COALESCE(sum(t.total), 0)
FROM  (
   SELECT week_start::date, LEAST(week_start::date + 6, _range_end) AS week_end
   FROM   generate_series(_range_start::timestamp
                        , _range_end::timestamp
                        , interval '1 week') week_start
   ) w
LEFT   JOIN tbl t ON t.mydate BETWEEN w.week_start and w.week_end
GROUP  BY w.week_start, w.week_end
ORDER  BY w.week_start
$func$;

Call:

SELECT * FROM f_tbl_weekly_sumtotals('2012-05-01', '2012-05-31');

Major points

• I wrapped it in a function for convenience, so the date range has to be provided once only.

• The subquery w produces the series of weeks starting from the first day of the given date range. The upper bound is capped with LEAST to stay within the upper bound of the given date range.

• Then LEFT JOIN to the data table (tbl in my example) to keep all weeks in the result, even where no data rows are found.

• The rest should be obvious. COALESCE to output 0 instead of NULL for empty weeks.

• Data types have to match, I assumed mydate date and total int for lack of information. (The sum() of an int is bigint.)

• Explanation for my particular use of generate_series():

  • Generating time series between two dates in PostgreSQL