CODEWITHSUNDEEP
sqlsql-server
sara
sara

Reputation: 534

Replace zero result with 1

I am calculating the average of passing from close to won. How can I to force any AVG result calculation that is zero to be 1, For example, in AE name its result is zero but i want it as 1

SELECT name AS OwnerId,
COALESCE(AVG([Opportunity.NumberOfDaysLivedinClose]),1) AS AvgTimeCtoW
FROM(SELECT
       [Opportunity.NumberOfDaysLivedinClose]
      ,name
       from [QueryExtract].[MLDataSource]
     ) as Opportunity
group by name

MLDataSource Table

name    Opportunity.NumberOfDaysLivedinClose
NS     0
NS     1
NS     0
NS     1
NS     0
NS     0
NS     0
NS     8

KB     31
KB     1
KB     53
KB     15

AE     0
AE     0
AE     1
AE     0
AE     0
AE     0
AE     0

AT     46
AT     71
AT     10

F      null

Expected Result

name      AvgTimeCtoW
AE              1
FS              1
NS              1
KB             25
AT             42

Upvotes: 0

Views: 6600

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I'm not sure why you would want to do this, but just use case():

SELECT name as OwnerId,
       (CASE WHEN AVG(o.NumberOfDaysLivedinClose) = 0 THEN 1
             ELSE AVG(o.NumberOfDaysLivedinClose)
        END) as AvgTimeCtoW
FROM [QueryExtract].[MLDataSource] o
GROUP BY name ;

The subquery is also unnecessary.

Upvotes: 1

Related Questions