INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

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

Which version of SQL Server do you have at hand?

You could do such a running summation this way:

CODE

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.

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

(OP)
Unfortunately I am using Server 2008 sad

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

SELECT @@version

2008, 2008r2?

Bye, Olaf.

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

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

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

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

CODE

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.

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

(OP)
Thanks Olaf,

Getting error on the query .

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

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.

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

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.

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

OK, I found the idea from https://raresql.com/2013/05/25/sql-server-faster-w...
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?

CODE

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/dev10.query?appId...

Quote (MSDN)

THIS TOPIC APPLIES TO: (✔)SQL Server (starting with 2008) (✔)Azure SQL Database (✔)Azure SQL Data Warehouse (✔)Parallel Data Warehouse

Bye, Olaf.

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

(OP)
Hi Olaf,

The compatibility_level is 100.

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

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

As Pinal Dave says:

Quote (Pinal Dave)

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.

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

(OP)
:(
Thanks Olaf. Still I need to find a another way.

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

(OP)
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

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close