How to write query SQL [ group by id order by date and reset value after nulll-CodePudding

I try write a query in SQL (Oracle) that result will be in column count

I use analytic function count(*) but the result is wrong.

I would like get the result like in column counts. Column counts is reset if column payment is 0 or null. Otherwhise add one to value in column counts

Table with output that I would like to get (targetcolumns --> counts) TARGET TABLE

date	id	payment	counts
20210131	111111111	0	0
20210228	111111111	0	0
20210331	111111111	0	0
20210430	111111111	100	1
20210531	111111111	200	2
20210630	111111111	400	3
20210731	111111111	0	0
20210830	111111111	0	0
20210930	111111111	0	0
20211031	111111111	200	1
20211130	111111111	500	2
20211231	111111111	0	0

After write this code I get this result.

select 
    date, id, payment,
    count(payment) over (partition by id order by date) as counts    
from 
    table

This is the incorrect result I'm getting now:

date	id	payment	counts
20210131	111111111	0	0
20210228	111111111	0	0
20210331	111111111	0	0
20210430	111111111	100	1
20210531	111111111	200	2
20210630	111111111	400	3
20210731	111111111	0	3
20210830	111111111	0	3
20210930	111111111	0	3
20211031	111111111	200	4
20211130	111111111	500	5
20211231	111111111	0	5

CodePudding user response：

So it seems you want to count the rows where payment is not zero? If that is the case, you can do it like this (using your query):

select 
    date, id, payment, 
    sum(case when payment = 0 then 0 else 1 end) over (partition by id order by date) as counts 
from table

And don't name columns using reserved words (like date), it can be done but a really bad habit.

CodePudding user response：

Sample data:

SQL> select * from test order by datum;

     DATUM         ID    PAYMENT
---------- ---------- ----------
  20210131          1          0
  20210228          1          0
  20210331          1          0
  20210430          1        100
  20210531          1        200
  20210630          1        400
  20210731          1          0
  20210830          1          0
  20210930          1          0
  20211031          1        200
  20211130          1        500
  20211231          1          0

12 rows selected.

Query that returns result you expect:

GRP CTE: determine groups of rows depending on payment column value within each id group (I presume there can be others)
final select: use case expression to set result to 0 if payment = 0; otherwise, use row_number analytic function

SQL> with grp as
  2    (select datum, id, payment,
  3       sum(case payment when 0 then 1 else 0 end) over (partition by id order by datum) grp
  4     from test
  5    )
  6  select datum, id, payment,
  7    case when payment = 0 then 0
  8         else row_number() over (partition by id, grp order by datum) - 1
  9    end rn
 10  from grp
 11  order by datum;

     DATUM         ID    PAYMENT         RN
---------- ---------- ---------- ----------
  20210131          1          0          0
  20210228          1          0          0
  20210331          1          0          0
  20210430          1        100          1
  20210531          1        200          2
  20210630          1        400          3
  20210731          1          0          0
  20210830          1          0          0
  20210930          1          0          0
  20211031          1        200          1
  20211130          1        500          2
  20211231          1          0          0

12 rows selected.

SQL>