maya.more <meena...@gmail.com> wrote: > 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 > > > pls let me know if anybody has any idea.
Maybe something like that: test=*# select * from d; d | i ------------+---- 2011-10-17 | 1 2011-10-18 | 2 2011-10-19 | 3 2011-10-20 | 4 2011-10-21 | 5 2011-10-22 | 6 2011-10-23 | 7 2011-10-24 | 8 2011-10-25 | 9 2011-10-26 | 10 2011-10-27 | 11 2011-10-28 | 12 2011-10-29 | 13 2011-10-30 | 14 2011-10-31 | 15 2011-11-01 | 16 2011-11-02 | 17 2011-11-03 | 18 2011-11-04 | 19 2011-11-05 | 20 (20 rows) Time: 0,276 ms test=*# select '2011-01-01'::date + (extract(doy from d) / 3)::int * '3day'::interval - '3day'::interval as "from", '2011-01-01'::date + (extract(doy from d) / 3)::int * '3day'::interval as "to", sum(i) from d group by 1,2 order by 1; from | to | sum ---------------------+---------------------+----- 2011-10-16 00:00:00 | 2011-10-19 00:00:00 | 6 2011-10-19 00:00:00 | 2011-10-22 00:00:00 | 15 2011-10-22 00:00:00 | 2011-10-25 00:00:00 | 24 2011-10-25 00:00:00 | 2011-10-28 00:00:00 | 33 2011-10-28 00:00:00 | 2011-10-31 00:00:00 | 42 2011-10-31 00:00:00 | 2011-11-03 00:00:00 | 51 2011-11-03 00:00:00 | 2011-11-06 00:00:00 | 39 (7 rows) it fails for more than 1 year, but i hope, you can modify my sql for your own. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql