Re: Count of records in a row - Mailing list pgsql-general

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)
;