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