Remove a Subquery in Postgresql

Question

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 output of this query is:

"D1";20.0000000000000000;25.0000000000000000
"D2";28.0000000000000000;
"D3";;

Is there a way to refactor the query by removing the subquery and replace it with a better construct in Postgresql?

Pg version? Thanks for including sample data as a CTE expression in the query. — Craig Ringer
– Craig Ringer, Commented Feb 12, 2013 at 7:45
Using the WITH clause to create sample data is cool as heck! — Matthew Cornell
– Matthew Cornell, Commented Oct 21, 2013 at 20:34

user330315 · Accepted Answer · 2013-02-12 08:15:26Z

2

SELECT DName,
       avg(case when loc = 'L1' then age else null end) as "L1 Avg",
       avg(case when loc = 'L2' then age else null end) as "L2 Avg"
FROM Dept d
LEFT JOIN Emp USING (DName)
GROUP BY DName

answered Feb 12, 2013 at 8:15

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Markus Mikkolainen Over a year ago

and maybe use "crosstab" to make the query nicer if you in actuality have more locations.

Gopal Over a year ago

Thanks horse. It was pretty concise query.

Collectives™ on Stack Overflow

Remove a Subquery in Postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related