Re: Group by on Date

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