On Tue, Oct 22, 2013 at 10:01 AM, Elliot <yields.falsehood@gmail.com> wrote: > On 2013-10-21 20:38, 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? >> >> > > It looks like you already found a solution, but here's one with a CTE. I > cobbled this together from an older query I had for doing something similar, > for which I unfortunately lost the original source of this approach. Also, > this implies that there is something that gives an ordering to these rows > (in this case, the field "i"). > > create temp table data (i int, val char); > > insert into data (val, i) > values > ('A',1), > ('A',2), > ('A',3), > ('B',4), > ('C',5), > ('A',6), > ('D',7), > ('A',8), > ('A',9), > ('D',10), > ('D',11), > ('B',12), > ('C',13), > ('C',14) > ; > > with x > as > ( > select i, > row_number() over () as xxx, > val, > row_number() over (partition by val order by i asc) > - row_number() over () as d > from data > order by i > ) > select val, > count(*) > from x > group by d, > val > order by min(i)
