-1

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
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
Vincent
  • 145
  • 2
  • 11

1 Answers1

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,
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • it worked and i add some function to avoid null values.```isnull (avg(case when elevationDifference < 0 then elevationDifference end),0) as eglAvgDepth, isnull (avg(case when elevationDifference > 0 then elevationDifference end),0) as pglAvgDepth ``` – Vincent Feb 11 '20 at 12:02