SQL query to put a number in a column and put an incremented number when there is a new text in a column

Question

I have a query SELECT * from TABLE which gives the result as below table:

Expected column is as below:

I want to frame a new column like whenever we get the value as 0 then the number should be incremented by 1. I tried DENSE_RANK() , ROW_NUMBER() but couldn't get the exact result which mentioned. Is that possible in PostgreSQL.

I am using a recursive query type to found that view. SO it will give the data like I mentioned. — Johnson Anthony, Aug 14 '20 at 10:11
I have added the answer. but there should be some field for ordering — Akhilesh Mishra, Aug 14 '20 at 10:30

Akhilesh Mishra · Answer 1 · 2020-08-14T10:27:28.737

Try This:

select name, value,
sum(case when value=0 then 1 else 0 end) over (order by  "sno") 
from (
select row_number() over() as "sno",* from example
     ) tab

DEMO

NOTE: Please note that there is no guaranteed that you will get same output always due no ordering field in your raw data.

So Better approach is to add some field in your view output by which it can be ordered and run the query like below:(assuming you have a ID field)

select 
  name, 
  value, 
  sum(case when value=0 then 1 else 0 end) over (order by id) 
from example

DEMO

SQL query to put a number in a column and put an incremented number when there is a new text in a column

1 Answers1