I am selecting data from multiple tables and want to insert data for missing years. Can I do this by using PLSQL or SQL ?
For example, below is output of my query:
YEAR COUNT
----- -----
2012 5
2013 6
2015 2
2016 1
2019 0
I want to add the year and count=0 for missing years and finally I should have this -
YEAR COUNT
----- -----
2012 5
2013 6
2014 0
2015 2
2016 1
2017 0
2018 0
2019 0
One option would be using connect by level <= .. syntax containing a left join with min(..) over (partition by ..) and max(..) over (partition by ..) analytic functions as quxiliary :
with t as
(
select t0.*,
min(year) over (partition by 1) as min_year,
max(year) over (partition by 1) as max_year
from t0
)
select t1.year as "year", nvl( t."count" , 0) as "count"
from
(
select distinct level + t.min_year - 1 as year
from t
connect by level <= t.max_year - t.min_year + 1 ) t1
left join t
on t.year = t1.year
order by "year"
Create a table years including the year values.
Create table years ( year int)
insert into years values (2010),(2011),(2012), ... etc
Then all you need to use is left join like below:
SELECT y.year,ISNULL(t.val,0)
FROM years y
LEFT OUTER JOIN yourtable t ON t.year=y.year
ORDER BY y.year
