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

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

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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related