count of values in sql

Question

I want to display the count of values when the value gets changed to 0 for the current record.

im not much familier with sql , tried self join but dint worked

see Input records below.

Date, value
2018-12, 0
2018-11, 1
2018-10, 1
2018-09, 1
2018-08, 0
2018-07, 0
2018-06, 1
2018-05, 0
2018-04, 1
2018-03, 0
2018-02, 0
2018-01, 1

output shroud be as below .

Date, value, Count
2018-12, 0 , 3
2018-11, 1 , 2
2018-10, 1 , 1
2018-09, 1 , 0
2018-08, 0 , 0
2018-07, 0 , 2
2018-06, 1 , 1
2018-05, 1 , 0
2018-04, 0 , 0
2018-03, 0 , 0
2018-02, 0 , 1
2018-01, 1 ,null

for the first record 2018-12, 0 we need to track when first occurrence of 0 came.once 0 came we need to stop count there.

In this case 0 came on 2018-08, 0 for the first record 2018-12, 0 . so we need to keep count 3 for 2018-12, 0.

For 2018-11, 1 we got 0 after two values so we need to keep 2.

For 2018-08, 0 we got 0 immediately so 0 need to keep.

For 2018-07, 0 we got 0 after two values so 2 need to keep like that.

Im not good at writing sql, please help how to write sql for this scenario.

you can try to use sum window function and lead window function — D-Shih
– D-Shih, Commented Mar 24, 2019 at 4:41
Thanks for the reply and I will check this tomorrow. If you get a chance could you provide the working query for this please — user2657426
– user2657426, Commented Mar 24, 2019 at 7:43

Barbaros Özhan · Accepted Answer · 2019-03-24 13:08:37Z

1

You need to assign a grouping. The grouping appears to be the number of 0s on or later than every row. You can calculate this using a cumulative sum.

Then, within each grouping, you can use row_number() for the calculation:

select t.*,
       (row_number() over (partition by grp order by "Date" asc) - 1) as counter
from (select t.*,
             sum(case when value = 0 then 1 else 0 end) over (order by "Date" desc) as grp
      from t
     ) t
order by "Date" desc;

I'm not sure why the earliest value would be NULL, so I didn't include that in the logic.

Here is a db<>fiddle (note this uses Postgres but the important code is the same).

edited Mar 24, 2019 at 13:08

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Mar 24, 2019 at 12:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user2657426 Over a year ago

This code worked like a champ!... thank you so much for this.. please ignore that Null value.

Collectives™ on Stack Overflow

count of values in sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related