Auto-Number and Cumulative sum in SQL Server Query results

Generate unique sequence numbers for a table for every group.

Let us consider a table as shown below.

use tempdb
go
create table mytable3 (col1 int, col2 datetime, uniq int)

insert into mytable3 select 1111,getdate(),1
insert into mytable3 select 1111,getdate() ,2
insert into mytable3 select 1111,getdate(),3 
insert into mytable3 select 1111,getdate() ,4
insert into mytable3 select 1111,getdate() ,5
insert into mytable3 select 1111,getdate() ,10
insert into mytable3 select 2222,getdate() ,120
insert into mytable3 select 2222,getdate() ,123
insert into mytable3 select 2222,getdate() ,1234
insert into mytable3 select 2222,getdate() ,1566
insert into mytable3 select 3333,getdate() ,1567
insert into mytable3 select 3333,getdate() ,1588
go

Query

Select * from mytable3

Results

Query

select col1,col2,col3=
Case when col1 = col1 then 
(select count(*) from mytable3 a where 
a.Col1 = mytable3.Col1 and a.uniq < mytable3.uniq)+1 end 
from mytable3

Results

Note: "Uniq" is a unique column.

Example 4:

Generate cumulative sum for a table. Let us consider the table shown below.

USE TEMPDB
GO
CREATE TABLE Inventory
(
pno char(12) NOT NULL,
movedate datetime NOT NULL,
qty int NOT NULL
)
GO
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/1/2004',100)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/2/2004',120)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/4/2004',-150)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/5/2004',50)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/06/2004',-35)
GO

Query

select * from Inventory

Results

Query

SELECT PNO,movedate,qty,(SELECT SUM(qty)
FROM Inventory AS x
WHERE y.movedate >= x.movedate) AS
qtyinhand
FROM Inventory AS y

Results

Note: "MoveDate" is a unique column.

Example 5:

Generate a cumulative sum for a table for every group. Let us consider the table shown below.

USE TEMPDB
GO
CREATE TABLE Inventory
(
pno char(12) NOT NULL,
movedate datetime NOT NULL,
qty int NOT NULL
)
GO
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/1/2004',100)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/2/2004',120)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/4/2004',-150)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/5/2004',50)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P01','03/06/2004',-35)
GO
INSERT INTO Inventory(pno,movedate,qty) VALUES('P02','03/7/2004',-150)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P02','03/8/2004',50)
INSERT INTO Inventory(pno,movedate,qty) VALUES('P02','03/9/2004',-35)
Go

Query

select * from Inventory

Results

Query

select pno,movedate,qty,cumulative=
Case when pno= pno then 
(select sum(qty) from inventory a where a.pno = inventory .pno and a.movedate 
<= inventory.movedate) end 
from inventory

Results

Note: "MoveDate" is a unique column.

Conclusion

As mentioned before, this article is meant to guide developers and database architects to use co-related sub-queries and/or identity functions to generate sequential numbers and cumulative summation in query results.