CODEWITHSUNDEEP
oracle-databasesequential
cmomah
cmomah

Reputation: 315

How do I add a column to the results that would show sequential numbers

How do I modify this SQL (or create a different SQL) such that it adds a column to the results - that would show sequential numbers that start with 1 and increases by 1

That is 1, 2, 3, 4 etc.

Note: I don't need to add an auto-increment column. I just need the incrementing column in the results if possible

select year, quarter, uploaddate, sum(wagecount) as wagecount from wage_reporting
group by year, quarter, uploaddate
order by year, quarter, uploaddate

Expected results would be something 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

Upvotes: 0

Views: 501

Answers (3)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

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

Upvotes: 2

ekochergin
ekochergin

Reputation: 4129

Using subquery and rownum pseudo column will help

select your_query.*, rownum
  from (select year, quarter, uploaddate, sum(wagecount) as wagecount 
          from wage_reporting
         group by year, quarter, uploaddate
         order by year, quarter, uploaddate) your_query

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521339

Use ROW_NUMBER:

SELECT year, quarter, uploaddate, SUM(wagecount) AS wagecount,
       ROW_NUMBER() OVER (ORDER BY uploaddate) sequential_numbers
FROM wage_reporting
GROUP BY year, quarter, uploaddate
ORDER BY year, quarter, uploaddate;

Upvotes: 1

Related Questions