views:

362

answers:

3

Hi folks,

I have three variables :-

@ScoreA DECIMAL(10,7)
@ScoreB DECIMAL(10,7)
@ScoreC DECIMAL(10,7)
@FinalScore DECIMAL(10, 7)

I wish to get the average of the three scores. BUT 1, 2 or all 3 values might be zero.

Eg. scenarios:

  • A = 1.4, B=3.5, C=5.0; FinalScore = 3.3
  • A = 0.0, B=0.0, C=0.0; FinalScore = 0.0
  • A = 1.1, B=0.0, C=0.0; FinalScore = 1.1
  • A = 0.0, B=2.0, C=4.8; FinalScore = 3.4

Cheers!

A: 
IF @A > 0 OR @B > 0 OR @C > 0
    SELECT ((@A + @B + @C) / 
        (0 + 
        CASE WHEN @A = 0 THEN 0 ELSE 1 END + 
        CASE WHEN @B = 0 THEN 0 ELSE 1 END + 
        CASE WHEN @C = 0 THEN 0 ELSE 1 END ))
ELSE
    SELECT 0.0

EDIT

Modified query to now handle divide by zero scenario's.

EDIT2

Here is "the trick with the AVG(..) function" :) with Common Table Expression

WITH T(I) AS (SELECT @A UNION SELECT @B UNION SELECT @C)
SELECT AVG(I) FROM T
WHERE I > 0
Svetlozar Angelov
hmm. I never thought of that. I was thinking it would be some trick with the AVG(..) function... brb :)
Pure.Krome
Nice. works :) Not sure why u have => (0 + <case stuff> ) ... that 0 + is .. redundant???
Pure.Krome
Yes, you're right, it doesn't need it first 0, my mistake..
Svetlozar Angelov
A: 
SELECT ((@A + @B + @C) / 
        (CASE WHEN (@A = 0.0 AND @B = 0.0 AND @C = 0.0) THEN 1 ELSE 0 END 
        + CASE WHEN @A = 0 THEN 0 ELSE 1 END 
        + CASE WHEN @B = 0 THEN 0 ELSE 1 END  
        + CASE WHEN @C = 0 THEN 0 ELSE 1 END 
        )
      )
kaiz.net
Sure about that? doesn't A + B + C / 0 .. occur .. if all the data was zero? that would be a *divide by zero* .. ??
Pure.Krome
Yes. In that case it would divide by 1.
kaiz.net
A: 

For me this is easier to read and understand:

DECLARE
    @ScoreA DECIMAL(10,7),
    @ScoreB DECIMAL(10,7),
    @ScoreC DECIMAL(10,7),
    @FinalScore DECIMAL(10, 7)

SET @ScoreA = 1.4
SET @ScoreB = 3.5
SET @ScoreC = 5.0

DECLARE 
    @AVG TABLE (value DECIMAL(10,7))

INSERT INTO @AVG
    SELECT @ScoreA WHERE @ScoreA > 0
    UNION
    SELECT @ScoreB WHERE @ScoreB > 0
    UNION
    SELECT @ScoreC WHERE @ScoreC > 0

SELECT COALESCE(AVG(value), 0) FROM @AVG
Grzegorz Gierlik