How to get AVG in CASE with condition?

Question

I have a table with integer values. They could be negative, 0, positive and NULL. I need treat NULL as 0, calculate average for a given date and if average value is less than 0 then put 0 there.

My query is the following:

select
    Id, 
    ValueDate,
    case 
        when avg(isnull(Value, 0)) > 0 then avg(isnull(Value, 0))
        else 0
    end AvgValue
from SomeTable
where ValueDate = @givenDate
group by Id, ValueDate

How to avoid double aggregate function definition in case statement (aggregate statement could be much more complex)?

I am pretty sure the `SQL Server` will cache the result of the function execution or the `AVG` calculation in your case won't be executed twice. — gotqn, Mar 24 '14 at 21:33
There would be a simple solution if TSQL had a scalar MAX() function. Perhaps you can implement your own: http://stackoverflow.com/questions/124417/is-there-a-max-function-in-sql-server-that-takes-two-values-like-math-max-in-ne — Turophile, Mar 24 '14 at 21:37

score 2 · Answer 1 · answered Mar 24 '14 at 21:23

2

I think the greatest function could help you:

select
    Id, 
    ValueDate,
    greatest(avg(isnull(Value, 0)),0) AvgValue
from SomeTable
where ValueDate = @givenDate
group by Id, ValueDate

answered Mar 24 '14 at 21:23

Lajos Veres

13,595
7
43
56

the question is was about the concept on how to handle cases like these. Imaging there instead of AVG there is a some function call. – Mando Mar 24 '14 at 21:27
Could you share a link to `greatest` function documentation for `T-SQL`? I have not used it and I am not able to found official reference to it. – gotqn Mar 24 '14 at 21:30
Ehh. It looks like it isn't exist... But you can define it. There is an example: http://www.sqlservercentral.com/Forums/Topic489858-338-1.aspx – Lajos Veres Mar 24 '14 at 21:33

score 1 · Accepted Answer · answered Mar 24 '14 at 21:58

This is a solution without creating implementation of any not build-in functions. I know your example will be more complex but this is just an idea:

CREATE TABLE DataSource
(
    [ID] TINYINT
   ,[Value] INT
)

INSERT INTO DataSource ([ID], [Value])
VALUES (1, 2)
      ,(1, 0)
      ,(1, NULL)
      ,(1, 98)
      ,(1, NULL)
      ,(2, -4)
      ,(2, 0)
      ,(2, 0)
      ,(2, NULL)

SELECT [ID]
      ,MAX([Value])
FROM
(
  SELECT  [ID]
         ,AVG(COALESCE([Value],0))
  FROM DataSource
  GROUP BY [ID]
  UNION ALL
  SELECT DISTINCT [ID]
                 ,0
  FROM DataSource
) Data([ID],[Value])
GROUP BY [ID]

Here is the fiddle - http://sqlfiddle.com/#!6/3d223/14

How to get AVG in CASE with condition?

2 Answers2