Multiple conditions in select statement based on values

Question

I have a multiple ctes. In my select statement I must filter values base on the conditions. This is my query.

SELECT roadName
    ,sum(roadLength) AS sumRoadLength
    ,avg(elevationDifference) AS eglAvgDepth
    ,avg(elevationDifference) AS pglAvgDepth
    ,
FROM cte3
GROUP BY roadName
ORDER BY roadName

Under "elevationDifference" there are lots of values ranging from -10 to +20 which are spread through "roadName". What i want to accomplished is that "eglAvgDepth" will return if all "elevationDifference" values are <0 and take the average. Same case with pglAvgDepth where values are >0.

I tried to add where statement but works only in eglAvgDepth

WHERE elevationDifference < 0
GROUP BY roadName
ORDER BY roadName

Answer 1

Just add a conditional expression:

avg(case when elevationDifference < 0 then elevationDifference end) as eglAvgDepth,
avg(case when elevationDifference > 0 then elevationDifference end) as pglAvgDepth,

EDIT:

You have phrased this that you want the value based on whether all the values are positive or negative. If so:

(case when max(elevationDifference) < 0 then avg(elevationDifference) end) as eglAvgDepth,
(case when max(elevationDifference) > 0 then avg(elevationDifference) end) as pglAvgDepth,