Count records that existed in a year for each year it has been issued

I have an Oracle table containing a number of active files. Each row is a file and has unique file # and issue date.

File # ISSUE_DATE
1254 15-OCT-1997
5245 22-MAY-2005
7852 02-APR-2015
9852 11-MAR-2021
etc

I want to query a count of how many files were active each year. So, if it was issued in 2010, then I want to include that file in the count for each year since it was issued (2010, 2011, 2012, etc), up to today’s date (sysdate).

I’d like my end table to loo like:

Year COUNT_OF_FILES
1997 20
1998 32
1999 55
2000 42
2019 130
2020 155
2021 151

I’ve been digging around stack for a while now, trying to find an example of this query, but I haven’t come across anything. I feel like this isn’t too difficult, but my Friday brain just isn’t firing on all cylinders. Any help or a point into a similar query would be much appreciated.

>Solution :

Group by year and return a running total

select Year, sum(n) over(order by year) COUNT_OF_FILES
from (
  SELECT EXTRACT(year FROM ISSUE_DATE) year, COUNT(*) n
  FROM tbl
  GROUP BY EXTRACT(year FROM ISSUE_DATE)
)

Leave a Reply