By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release | Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 |
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release | 0 |
Create Table PHONES AS
( Select 1131 "ID", 4515551111 "PHONE_NUMBER" From Dual Union All
Select 1154, 4400051133 From Dual Union All
Select 1214, 4400051133 From Dual Union All
Select 1243, 5555555555 From Dual Union All
Select 1511, 5555555555 From Dual Union All
Select 1631, 5555555555 From Dual Union All
Select 1965, 3333051133 From Dual
);
7 rows affected
Select ID, PHONE_NUMBER,
ROW_NUMBER() Over(Partition By PHONE_NUMBER Order By ID) "INSTANCE_ROW_NUMBER",
Count(ID) Over(Partition By PHONE_NUMBER Order By ID) "INSTANCE_COUNT",
Sum(1) Over(Partition By PHONE_NUMBER Order By ID Rows Between Unbounded Preceding And Current Row) "INSTANCE_SUM_1"
From PHONES
Order By ID
| ID | PHONE_NUMBER | INSTANCE_ROW_NUMBER | INSTANCE_COUNT | INSTANCE_SUM_1 |
|---|---|---|---|---|
| 1131 | 4515551111 | 1 | 1 | 1 |
| 1154 | 4400051133 | 1 | 1 | 1 |
| 1214 | 4400051133 | 2 | 2 | 2 |
| 1243 | 5555555555 | 1 | 1 | 1 |
| 1511 | 5555555555 | 2 | 2 | 2 |
| 1631 | 5555555555 | 3 | 3 | 3 |
| 1965 | 3333051133 | 1 | 1 | 1 |