Count where column changes to specific value in postgres

Question

I need a count of rows where action=1 and action is different from the above row. The first row should count if action=1.

     action_date     | action 
---------------------+--------
 2017-01-01 00:00:00 |      1
 2017-01-02 00:00:00 |      1
 2017-01-03 00:00:00 |      0
 2017-01-04 00:00:00 |      0
 2017-01-05 00:00:00 |      1
 2017-01-06 00:00:00 |      0
 2017-01-07 00:00:00 |      1

In this example the 1st, 5th, and 7th rows count and the result should be 3. Any help is much appreciated.

Vamsi Prabhala · Accepted Answer · 2017-03-13 18:32:27Z

4

Use lag to get the value on previous row and count thereafter based on the conditions.

select count(*)
from (select action_date,action,lag(action) over(order by action_date) as prev_action
      from t
     ) t
where (action<>prev_action and action=1) or (action=1 and prev_action is null)

Or it can be simplified as

select 
count(case when lag(action) over(order by action_date) is null then and action = 1 then 1
           when lag(action) over(order by action_date) is not null and lag(action) over(order by action_date) <> action and action = 1 then 1 
      end) as cnt
from t

edited Mar 13, 2017 at 18:32

answered Mar 13, 2017 at 18:17

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rurp Over a year ago

Thank you for the quick response. The answer does not include count the very first row. How can that be included?

Biggert Over a year ago

The simplified answer doesn't seem to be correct syntax (focusing on the "then and" part. After fixing this, I get an exception that aggregate function calls cannot contain window function calls.

Collectives™ on Stack Overflow

Count where column changes to specific value in postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related