Postgresql 9.4 incrementing value issue I am trying to go from the first table: test4 (pid, event, time) and count how many events transition to another (in second table eventtransitions).

For instance, in the final table, there should be a +1 count for prev = A and event = B because the first two rows has A happening before B and the pid for both are 1. The issue I am having right now is that my query only increments once, but should actually increment as many times as the transitions occur. The final table should have prev = A event = B with count = 2.  

Test4 table:

 pid | event |        time         
-----+-------+---------------------
   1 | A     | 2016-09-23 02:21:13
   1 | B     | 2016-09-23 03:24:13
   1 | A     | 2016-09-23 04:21:13
   1 | B     | 2016-09-23 05:21:13


Evventtransitions table:

 prev | event | count 
------+-------+-------
 C    | B     |     0
 C    | A     |     0
 A    | C     |     0
 B    | C     |     0
 A    | B     |     1
 B    | A     |     1


The query I tried is this: 

update eventtransitions
    set count = count + 1
from 
(select rank() over (order by pid, time) as rank,
    pid, event, time
    from test4) as a 
join
(select rank() over (order by pid, time) as rank,
    pid, event, time
    from test4) as b
    on a.rank = b.rank - 1
    where eventtransitions.event = b.event and prev = a.event
    and a.pid = b.pid;
Use the window function lag() in a subquery:

UPDATE eventtransitions e
SET    count = COALESCE(count, 0) + t.count
FROM (
   SELECT prev, event, count(*) AS count
   FROM  (
      SELECT lag(pid)   OVER w AS prev_pid, pid
           , lag(event) OVER w AS prev    , event
      FROM   test4
      WINDOW w AS (ORDER BY time)
      ) t
   WHERE   prev_pid = pid  -- only where pid matches
   GROUP   BY prev, event
   ) t
WHERE  e.prev = t.prev
AND    e.event = t.event;


Seems like you only want to count rows cases where the previous row has the same pid. I eliminated all other.

Aggregate in the next step to get counts.  (That's what you were missing!) Then add the result to the existing count in eventtransitions.

COALESCE to catch possibly existing NULL values in eventtransitions.