sql - Remove a Subquery in Postgresql -

sql - Remove a Subquery in Postgresql -

with dept(dname) ( values ('d1'), ('d2'), ('d3') ) , emp(dname, ename, age, loc) ( values ('d1','e11',20,'l1'), ('d1','e12',25,'l2'),('d2','e21',28,'l1') ) select dname, (select avg(age) emp loc = 'l1' , dname = d.dname) "l1 avg", (select avg(age) emp loc = 'l2' , dname = d.dname) "l2 avg" dept d left bring together emp using (dname) grouping dname

the output of query is:

"d1";20.0000000000000000;25.0000000000000000 "d2";28.0000000000000000; "d3";;

is there way refactor query removing subquery , replace improve build in postgresql?

select dname, avg(case when loc = 'l1' age else null end) "l1 avg", avg(case when loc = 'l2' age else null end) "l2 avg" dept d left bring together emp using (dname) grouping dname

sql postgresql join subquery