Auto-Number and Cumulative sum in SQL Server Query results

Wednesday Jul 7th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

SQL Server developers and database architects often find they have a need to sequence query results or generate a cumulative sum for a group of rows in a table. Learn how to use co-related sub-queries and/or identity functions to generate such sequential numbers and cumulative summations in query results.

SQL Server developers and database architects often find they have a need to sequence query results or generate a cumulative sum for a group of rows in a table. SQL Server does not have a Pseudo row-id similar to other RDBMS.

In this article, I am going to guide the developers and database architects to use co-related sub-queries and/or identity functions to generate such sequential numbers and cumulative summations in query results.

Example 1:

Generate sequential number for a table, which has at least one unique numeric column.

Let us consider we have a table as shown below.

Use tempdb
go
Create table Mytable1 (au_id int, authors_name varchar(100))
Go
insert into MyTable1 select 100,'Mathew Arnold'
insert into MyTable1 select 140,'Keith Davis'
insert into MyTable1 select 76,'David Taylor'
insert into MyTable1 select 127,'Agatha Christy'
insert into MyTable1 select 12,'Sidney Sheldon'
go

Query

select au_id,authors_name from Mytable1 order by au_id

Results

12

Sidney Sheldon

76

David Taylor

100

Mathew Arnold

127

Agatha Christy

140

Keith Davis

au_id is unique in this table, so it is easy to use a co-related sub-query to generate sequential numbers.

Query

SELECT (SELECT count(au_id) FROM Mytable1 AS x WHERE x.au_id<= y.au_id) AS
Sequence, au_id,authors_name
FROM Mytable1 AS y order by au_id

Results

1

12

Sidney Sheldon

2

76

David Taylor

3

100

Mathew Arnold

4

127

Agatha Christy

5

140

Keith Davis

Note: "au_id" is a unique column.

Example 2:

Generate unique sequence numbers for a table that has no unique column.

Let us consider the table shown below. For tables with no unique columns, it is easy to use the identity function to generate unique sequence numbers.

Use tempdb
go
Create table Mytable2 (au_id int, authors_name varchar(100))
Go
insert into MyTable2 select 100,'Mathew Arnold'
insert into MyTable2 select 140,'Keith Davis'
insert into MyTable2 select 76,'David Taylor'
insert into MyTable2 select 127,'Agatha Christy'
insert into MyTable2 select 12,'Sidney Sheldon'
insert into MyTable2 select 12,'Mcarthur'
insert into MyTable2 select 76,'Alan Smiles'
insert into MyTable2 select 100,'Kreisler'
go

Query

select * from mytable2 order by au_id

Results

12

Sidney Sheldon

12

Mcarthur

76

Alan Smiles

76

David Taylor

100

Mathew Arnold

100

Kreisler

127

Agatha Christy

140

Keith Davis

Query

select identity(int,1,1) as Sequence, au_id,authors_name into #x from Mytable2 order by au_id
go
select * from #x
go
drop table #x
go

Results

1

12

Sidney Sheldon

2

12

Mcarthur

3

76

Alan Smiles

4

76

David Taylor

5

100

Mathew Arnold

6

100

Kreisler

7

127

Agatha Christy

8

140

Keith Davis

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved