In article <1318661510830-4904685.post@n5.nabble.com>,
"maya.more" <meena.mk@gmail.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