select t2.dep_no,t2.dep_name,t1,empCount,t2.empAvgAge
from(
select t.dep_no, count(t.emp_no) as empCount, avg(t.emp_age) empAvgAge,
from employee t
where t.emp_no < 80707999
group by t.dep_no
)t1
left join department t2 on t1.dep_no=t2.dep_no
from(
select t.dep_no, count(t.emp_no) as empCount, avg(t.emp_age) empAvgAge,
from employee t
where t.emp_no < 80707999
group by t.dep_no
)t1
left join department t2 on t1.dep_no=t2.dep_no
