Re: Count of records in a row

From: David Johnston <polobo(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Count of records in a row
Date: 2013-10-22 00:54:14
Message-ID: 1382403254297-5775365.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ken Tanzer 2013-10-22 00:56:43 Re: pg_dump doesn't restore on newer database due to schema issues (bug or limitation?)
Previous Message Robert James 2013-10-22 00:38:52 Count of records in a row