In article <1318661510830-4904685.p...@n5.nabble.com>, "maya.more" <meena...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql