How to increase a counter for each changed rows in SQL query

[burakcan]

Hello,

I need to increase a counter for each changed rows in SQL query .
I have a column holds values 0 or 1. Id like to create a new column based on the column C which holds 0 or 1. If the value is 0 then my new value will be 0 if it is 1 then my new column value at the first 1 then for the each value 1 will increase 1 (1,2,3...)

Please see the attached file. I have A,B,C columns and I'd like to create the column D.

Any help greatly appropriated.

 

[Olaf Doschke]

Which version of SQL Server do you have at hand?

You could do such a running summation this way:

Declare @testdata as Table (ForeignID int, ZeroOrOne tinyint);
Insert Into @testdata values (555,0),(555,0),(555,1),(555,0),(555,1),(555,1),(555,0),(555,1),(555,0);

Select ForeignID, ZeroOrOne, Sum(ZeroOrOne) Over (PARTITION BY ForeignID ORDER BY ForeignID ROWS UNBOUNDED PRECEDING) as RunningSum From @testdata

The ROWS UNBOUNDED PRECEDING clause exists since SQL2012.

Bye, Olaf.

 

[burakcan]

Unfortunately I am using Server 2008 :-(

 

[Olaf Doschke]

SELECT @@version

2008, 2008r2?

Bye, Olaf.

 

[burakcan]

(No column name)
Microsoft SQL Server 2008 R2 (SP3)

 

[Olaf Doschke]

OK, let's see what you could do instead...

Declare @testdata as Table (ForeignID int, ZeroOrOne tinyint);
Insert Into @testdata values (555,0),(555,0),(555,1),(555,0),(555,1),(555,1),(555,0),(555,1),(555,0);

With numbered as
(
  Select *, Row_Number() Over (ORDER BY ForeignID) as RowNum From @testdata 
)

Select ForeignID, ZeroOrOne, Sum(ZeroOrOne) Over (PARTITION BY ForeignID ORDER BY ForeignID, RowNum) as RunningSum From numbered

Bye, Olaf.

 

[burakcan]

Thanks Olaf,

Getting error on the query .

 

[Olaf Doschke]

I tested this, can you double check you copied it correctly?

proof of concept working:

Did you forget your last statement before a CTE (With...) has to be terminated with a semicolon, just like in my code?

Bye, Olaf.

 

[Olaf Doschke]

By the way, you don't need the CTE at all, if your data has a value unique per row, like the primary key column, which you can use as ORDER BY column, even as first and only to order by. On the same thought, you don't necessarily need to partition data, that only resets the running sum at rows having a change in that value. My sample data is not good at all, as all foreign ids are same it has no real data driven order, still fetches rows as inserted. If you want a runnning sum, you need some order anyway, so you surely have something.

Adapt this to your situation, this isn't and can't be universally used exactly this way. I just plagiated your ID 55555555, you have a better Date column to sort by.

Bye, Olaf.

 

[burakcan]

Hi Olaf,

Please see the screen shot to see the error messages. It works in server 2012 but not in 2008 R2.
I need to do this in server 2008.
Also this is just an example data I need to order by DATE and ID

Please advise...

 

[Olaf Doschke]

OK, I found the idea from

https://raresql.com/2013/05/25/sql-server-faster-way-to-calculate-running-total/

Explicitly stating this type of OVER clause would be possible in 2008R2 or later. See method 3.

Are you sure, your 2008R2 is not in a compatibility mode?

SELECT name, compatibility_level FROM sys.databases;

See whether that database just runs in 2005 or lower compatibility mode.

Books Online also state this topic applies to SQL Server starting with 2008, just the normal OVER clause:

https://msdn.microsoft.com/query/de...LLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true

THIS TOPIC APPLIES TO: [highlight #8AE234](✔)[/highlight]SQL Server (starting with 2008) [highlight #8AE234](✔)[/highlight]Azure SQL Database [highlight #8AE234](✔)[/highlight]Azure SQL Data Warehouse [highlight #8AE234](✔)[/highlight]Parallel Data Warehouse

Bye, Olaf.

 

[burakcan]

Hi Olaf,

The compatibility_level is 100.

 

[Olaf Doschke]

100 is just a tiny bit back, that database then is in 2008 mode, while the server is 2008R2. Should be sufficient anyway regarding the applies to specifications, but then see whether this works in normal mode with a new database on that 2008R2 server.

You find more possibilities for running sums googling, eg

https://blog.sqlauthority.com/2014/10/04/sql-server-how-to-find-running-total-in-sql-server/

As Pinal Dave says:

The query is very expensive.

Meaning: expensive performancewise. Everything seems fast with just a bunch of rows, but not with even just tens of thousands and not to talk of millions.

Bye, Olaf.

 

[burakcan]

Thanks Olaf. Still I need to find a another way.

 

[Olaf Doschke]

If your only solution is the slow one, then you have to take it...

https://blog.sqlauthority.com/2014/10/04/sql-server-how-to-find-running-total-in-sql-server/

Bye, Olaf.

 

[burakcan]

Hi Olaf,

Thank you very much for your help.

I am using below script and it seems working

;with cte as
(SELECT a.[ApplicationID]
, a.[DecisionTypeDetailDate]
, SUM(ConVert(Int,b.[ResubmitTOD])) AS ResubFreqTOD
, SUM(ConVert(Int,b.[ResubmitTOS])) AS ResubFreqTOS
FROM [OTDM].[cco].[FactPRSDetail] a INNER JOIN [OTDM].[cco].[FactPRSDetail] b
ON a.[ApplicationID] = b.[ApplicationID]
AND a.[DecisionTypeDetailDate] >= b.[DecisionTypeDetailDate]
where a.ApplicationID = 46344639
GROUP BY a.[ApplicationID], a.[DecisionTypeDetailDate]
)

,
cte1 as (select [ApplicationID]
,[DecisionTypeDetailDate]
,ResubFreqTOD
,ResubFreqTOS
,ROW_NUMBER() over (partition by [DecisionTypeDetailDate] ORDER BY ResubFreqTOD desc) as row_num
from cte)
select [ApplicationID],[DecisionTypeDetailDate],ResubFreqTOS,ResubFreqTOD from cte1 where row_num=1

 

[Olaf Doschke]

This puzzles me:

where row_num=1 will only pick one final row, doesn't it? Then for what do you need a running sum, you just seem to need a plain normal total sum, that needs no extra subqueries.

Bye, Olaf.