Re: Group by on Date

In article <1318661510830-4904685(dot)post(at)n5(dot)nabble(dot)com>,
"maya.more" <meena(dot)mk(at)gmail(dot)com> writes:

> I have a table with Date and unit column. . I want to find sum of unit column
> considering 3 days each

> User will specify start and enddate

> Eg

> Date Unit
> 10/1/2011 1
> 10/2/2011 2
> 10/3/2011 3
> 10/4/2011 4
> 10/5/2011 4
> 10/6/2011 1
> 10/7/2011 2
> 10/8/2011 3
> 10/9/2011 1
> 10/10/2011 1
> 10/11/2011 1
> suppose if user selects date 10/1/2011 to 10/6/2011

> then output should be

> start date enddate unit
> 10/1/2011 10/3/2011 6
> 10/4/2011 10/6/2011 9

SELECT ('2011-10-01'::date + INTERVAL '3 day' * ((date - '2011-10-01') / 3))::date AS start_date,
('2011-10-01'::date + INTERVAL '2 day' + INTERVAL '3 day' * ((date - '2011-10-01') / 3))::date AS end_date,
sum(unit) AS unit
FROM tbl
WHERE date BETWEEN '2011-10-01' AND '2011-10-06'
GROUP BY 1, 2
ORDER BY 1