CODEWITHSUNDEEP
oracle-databasegroup-bysequential
cmomah
cmomah

Reputation: 315

Adding a column to the results that would show sequential numbers grouped based on year

These SQL are able to add a column that has incrementing numbers (1, 2, 3, 4, etc.)

select year, quarter, uploaddate, wagecount, SUM(1) 
OVER (ORDER BY year, quarter, uploaddate) as SequentialNumbers from SAMPLE_DATA
order by year, quarter, uploaddate

or

select your_query.*, rownum
  from (select year, quarter, uploaddate, wagecount 
          from SAMPLE_DATA) your_query

Result look like this

Year Quarter UploadDate WageCount SequentialNumbers
2021 1 01-JAN-21 2 1
2021 1 02-JAN-21 2 2
2021 1 03-JAN-21 1 3
2021 1 04-JAN-21 5 4

Is there anyway to take the next bold step of making each year have its own sequential numbers. In other words results would ultimately look like this

something like this

Year Quarter UploadDate WageCount SequentialNumbers
2019 1 01-JAN-21 2 1
2019 1 02-JAN-21 2 2
2019 1 03-JAN-21 1 3
2019 1 04-JAN-21 5 4
2020 1 01-JAN-20 2 1
2020 1 02-JAN-20 2 2
2020 1 03-JAN-20 1 3
2020 1 04-JAN-20 5 4
2021 1 01-JAN-21 2 1
2021 1 02-JAN-21 2 2
2021 1 03-JAN-21 1 3
2021 1 04-JAN-21 5 4

Sample data is as follows:

CREATE TABLE SAMPLE_DATA (
    YEAR VARCHAR2(4) NULL,
    QUARTER NUMBER(1,0) NULL,
    UPLOADDATE DATE NULL,
    WAGECOUNT NUMBER(10,0) NULL
);

Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('27-MAR-19','DD-MON-RR'),5);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('28-MAR-19','DD-MON-RR'),8493);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('29-MAR-19','DD-MON-RR'),15070);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('30-MAR-19','DD-MON-RR'),1244);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('03-JAN-20','DD-MON-RR'),0);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('05-JAN-20','DD-MON-RR'),2);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('06-JAN-20','DD-MON-RR'),3);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('07-JAN-20','DD-MON-RR'),6);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('21-APR-21','DD-MON-RR'),59);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('22-APR-21','DD-MON-RR'),10);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('23-APR-21','DD-MON-RR'),16);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('24-APR-21','DD-MON-RR'),1);

Upvotes: 0

Views: 29

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

You need row_number analytic function:

select year, quarter, uploaddate, wagecount, 
       row_number()
         OVER (partition by year
               ORDER BY quarter, uploaddate) as SequentialNumbers 
from SAMPLE_DATA
order by year, quarter, uploaddate;

Results:

YEAR    QUARTER UPLOADDATE           WAGECOUNT SEQUENTIALNUMBERS
---- ---------- ------------------- ---------- -----------------
2019          1 2019-03-27 00:00:00          5                 1
2019          1 2019-03-28 00:00:00       8493                 2
2019          1 2019-03-29 00:00:00      15070                 3
2019          1 2019-03-30 00:00:00       1244                 4
2020          1 2020-01-03 00:00:00          0                 1
2020          1 2020-01-05 00:00:00          2                 2
2020          1 2020-01-06 00:00:00          3                 3
2020          1 2020-01-07 00:00:00          6                 4
2021          2 2021-04-21 00:00:00         59                 1
2021          2 2021-04-22 00:00:00         10                 2
2021          2 2021-04-23 00:00:00         16                 3
2021          2 2021-04-24 00:00:00          1                 4

Upvotes: 1

Related Questions