In article <[email protected]>,
"maya.more" <[email protected]> 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
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
