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

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:

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.

CodePudding user response：

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)
)