Problem
You want to return the number of employees hired each year for the entire decade of the 1980s, but there are some years in which no employees were hired. You would like to return the following result set:
YR CNT
---- ----------
1980 1
1981 10
1982 2
1983 1
1984 0
1985 0
1986 0
1987 0
1988 0
1989 0Solution
The trick to this solution is returning zeros for years that saw no employees hired. If no employee was hired in a given year, then no rows for that year will exist in table EMP. If the year does not exist in the table, how can you return a count, any count, even zero? The solution requires you to outer join. You must supply a result set that returns all the years you want to see, and then perform a count against table EMP to see if there were any employees hired in each of those years.
DB2
Use table EMP as a pivot table (because it has 14 rows) and the built-in function YEAR to generate one row for each year in the decade of 1980. Outer join to table EMP and count how many employees were hired each year:
1 select x.yr, coalesce(y.cnt,0) cnt
2 from (
3 select year(min(hiredate)over()) -
4 mod(year(min(hiredate)over()),10) +
5 row_number()over()-1 yr
6 from emp fetch first 10 rows only
7 ) x
8 left join
9 (
10 select year(hiredate) yr1, count(*) cnt
11 from emp
12 group by year(hiredate)
13 ) y
14 on ( x.yr = y.yr1 )Oracle
1 select x.yr, coalesce(cnt,0) cnt
2 from (
3 select extract(year from min(hiredate)over()) -
4 mod(extract(year from min(hiredate)over()),10) +
5 rownum-1 yr
6 from emp
7 where rownum <= 10
8 ) x
9 left join
10 (
11 select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
12 from emp
13 group by to_number(to_char(hiredate,'YYYY'))
14 ) y
15 on ( x.yr = y.yr )PostgreSQL and MySQL
Use table T10 as a pivot table (because it has 10 rows) and the built-in function EXTRACT to generate one row for each year in the decade of 1980. Outer join to table EMP and count how many employees were hired each year:
1 select y.yr, coalesce(x.cnt,0) as cnt
2 from (
3 selectmin_year-mod(cast(min_year as int),10)+rn as yr
4 from (
5 select (select min(extract(year from hiredate))
6 from emp) as min_year,
7 id-1 as rn
8 from t10
9 ) a
10 ) y
11 left join
12 (
13 select extract(year from hiredate) as yr, count(*) as cnt
14 from emp
15 group by extract(year from hiredate)
16 ) x
17 on ( y.yr = x.yr )SQL Server
Use table EMP as a pivot table (because it has 14 rows) and the built-in function YEAR to generate one row for each year in the decade of 1980. Outer join to table EMP and count how many employees were hired each year:
1 select x.yr, coalesce(y.cnt,0) cnt
2 from (
3 select top (10)
4 (year(min(hiredate)over()) -
5 year(min(hiredate)over())%10)+
6 row_number()over(order by hiredate)-1 yr
7 from emp
8 ) x
9 left join
10 (
11 select year(hiredate) yr, count(*) cnt
12 from emp
13 group by year(hiredate)
14 ) y
15 on ( x.yr = y.yr )
Discussion
Despite the difference in syntax, the approach is the same for all solutions. Inline view X returns each year in the decade of the ’80s by first finding the year of the earliest HIREDATE. The next step is to add RN–1 to the difference between the earliest year and the earliest year modulus ten. To see how this works, simply execute inline view X and return each of the values involved separately. Listed below is the result set for inline view X using the window function MIN OVER (DB2, Oracle, SQL Server) and a scalar subquery (MySQL, PostgreSQL):
select year(min(hiredate)over()) -
mod(year(min(hiredate)over()),10) +
row_number()over()-1 yr,
year(min(hiredate)over()) min_year,
mod(year(min(hiredate)over()),10) mod_yr,
row_number()over()-1 rn
from emp fetch first 10 rows only
YR MIN_YEAR MOD_YR RN
---- ---------- ---------- ----------
1980 1980 0 0
1981 1980 0 1
1982 1980 0 2
1983 1980 0 3
1984 1980 0 4
1985 1980 0 5
1986 1980 0 6
1987 1980 0 7
1988 1980 0 8
1989 1980 0 9
select min_year-mod(min_year,10)+rn as yr,
min_year,
mod(min_year,10) as mod_yr
rn
from (
select (select min(extract(year from hiredate))
from emp) as min_year,
id-1 as rn
from t10
) x
YR MIN_YEAR MOD_YR RN
---- ---------- ---------- ----------
1980 1980 0 0
1981 1980 0 1
1982 1980 0 2
1983 1980 0 3
1984 1980 0 4
1985 1980 0 5
1986 1980 0 6
1987 1980 0 7
1988 1980 0 8
1989 1980 0 9Inline view Y returns the year for each HIREDATE and the number of employees hired during that year:
select year(hiredate) yr, count(*) cnt
from emp
group by year(hiredate)
YR CNT
----- ----------
1980 1
1981 10
1982 2
1983 1Finally, outer join inline view Y to inline view X so that every year is returned even if there are no employees hired.
