Remove subquery in Postgresql
WITH Dept(DName) AS (
VALUES ('D1'), ('D2'), ('D3')
) ,
Emp(DName, EName, Age, Loc) AS (
VALUES ('D1','E11',20,'L1'), ('D1','E12',25,'L2'),('D2','E21',28,'L1')
)
SELECT DName,
(SELECT avg(Age)
FROM Emp
WHERE Loc = 'L1'
AND DName = d.DName) AS "L1 Avg",
(SELECT avg(Age)
FROM Emp
WHERE Loc = 'L2'
AND DName = d.DName) AS "L2 Avg"
FROM Dept d
LEFT JOIN Emp USING (DName)
GROUP BY DName
The result of this query:
"D1";20.0000000000000000;25.0000000000000000
"D2";28.0000000000000000;
"D3";;
Is there a way to refactor the query by removing the subquery and replacing it with a better construct in Postgresql?
+3
source to share