Auto-Number and Cumulative sum in SQL Server Query results - Page 2

Example 3:

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

1111	4/19/04 1:42 PM	1
1111	4/19/04 1:42 PM	2
1111	4/19/04 1:42 PM	3
1111	4/19/04 1:42 PM	4
1111	4/19/04 1:42 PM	5
1111	4/19/04 1:42 PM	10
2222	4/19/04 1:42 PM	120
2222	4/19/04 1:42 PM	123
2222	4/19/04 1:42 PM	1234
2222	4/19/04 1:42 PM	1566
3333	4/19/04 1:42 PM	1567
3333	4/19/04 1:42 PM	1588

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

1111	4/19/04 1:42 PM	1
1111	4/19/04 1:42 PM	2
1111	4/19/04 1:42 PM	3
1111	4/19/04 1:42 PM	4
1111	4/19/04 1:42 PM	5
1111	4/19/04 1:42 PM	6
2222	4/19/04 1:42 PM	1
2222	4/19/04 1:42 PM	2
2222	4/19/04 1:42 PM	3
2222	4/19/04 1:42 PM	4
3333	4/19/04 1:42 PM	1
3333	4/19/04 1:42 PM	2

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

P01	3/1/04 12:00 AM	100
P01	3/2/04 12:00 AM	120
P01	3/4/04 12:00 AM	-150
P01	3/5/04 12:00 AM	50
P01	3/6/04 12:00 AM	-35

Query

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

Results

P01	3/1/04 12:00 AM	100	100
P01	3/2/04 12:00 AM	120	220
P01	3/4/04 12:00 AM	-150	70
P01	3/5/04 12:00 AM	50	120
P01	3/6/04 12:00 AM	-35	85

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

P01	3/1/04 12:00 AM	100
P01	3/2/04 12:00 AM	120
P01	3/4/04 12:00 AM	-150
P01	3/5/04 12:00 AM	50
P01	3/6/04 12:00 AM	-35
P02	3/7/04 12:00 AM	-150
P02	3/8/04 12:00 AM	50
P02	3/9/04 12:00 AM	-35

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

P01	3/1/04 12:00 AM	100	100
P01	3/2/04 12:00 AM	120	220
P01	3/4/04 12:00 AM	-150	70
P01	3/5/04 12:00 AM	50	120
P01	3/6/04 12:00 AM	-35	85
P02	3/7/04 12:00 AM	-150	-150
P02	3/8/04 12:00 AM	50	-100
P02	3/9/04 12:00 AM	-35	-135

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.

» See All Articles by Columnist MAK