Robert James wrote > I have a table of event_id, event_time. Many times, several events > happen in a row. I'd like a query which replaces all of those events > with a single record, showing the count. > > Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1; > D,1; A,2; D,2; B,1; C,2 > > How can I do that?
<Theory Only> Window functions are going to be your friend. To solve the grouping problem I would assign the first row's value a group value of zero (0). Using the "lag(...)" window function and an appropriately defined frame you conditionally add one (1) to the prior row's group value if the value of lag(1) does not equal the current row's value. The result should be a new column where all sequential duplicates share the same group number. Distinct will give you a lookup relation for which letter belongs to which group Group By + Count on the group will give you counts Use string_agg(...) to condense the above into single row/column HTH David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Count-of-records-in-a-row-tp5775363p5775365.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
