2013/6/1 Shenli Zhu <zhushe...@gmail.com> > Hi, there are 2 columns, flag(boolean) and num(integer),in a table. > Table is like > | flag | num | > |------+-----| > | 1 | 2 | \ 5 > | 1 | 3 | / > | 0 | 1 | \ 7 > | 0 | 6 | / > | 1 | 4 | \ 9 > | 1 | 5 | / > | ... | ... | > I want to sum up the raw with the same continuous flags. E.g. flag in 1st > and > 2nd row are both 1, 3rd and 4th are both 0, 5th and 6th are both 1. So > the table becomes > | flag | num | > |------+-----| > | 1 | 5 | > | 0 | 7 | > | 1 | 9 | > > Can I do this in SQL or PL/pgSQL? Any suggestions are welcome. >
WITH data(flag,num) AS (VALUES (true,2),(true, 3), (false,1),(false,6), (true,4),(true,5)) SELECT flag, sum(num) AS sum_num FROM ( SELECT flag,num, sum(grp_flag) OVER (ORDER BY rn) AS grp FROM ( SELECT flag,num, row_number() OVER() AS rn, CASE WHEN lag(flag) OVER () = flag THEN NULL ELSE 1 END AS grp_flag FROM data ) s1 ) s2 GROUP BY grp,flag ORDER BY grp; You should introduce some explicit ordering column into your table though, as results will change otherwise based on your DB activity. Inspired by this answer: http://stackoverflow.com/a/10624628/1154462 -- Victor Y. Yegorov