Average a column without using aggregate function in sql server

Transact-SQL
1

Hi,
Help me to solve the problem
Regards
Prabhu

2

  1. That will be impossible, as it is defined as the SUM of all values divided by their COUNT, which are 2 aggregate functions.

  2. You didn't explain why you need this. It sounds like a homework or interview question, and a trick one at that.

  3. If there is an actual solution, it can't be easier than simply using AVG().

3

Dynamic sql, cursor or function summing on local variables would probably do it.

But as @robert_volk wrote ---> why?

4

Hi,
Question was asked in interview. They asked to use function in sql server.

5

To get an average without using the AVG function itself, but using other aggregate functions, is very easy. But using no aggregate functions at all would be very difficult, perhaps impossible, I'm not sure ... maybe some mathematical trick with EXP() function???

6

Hi Prabhu,

I tried to calculate average using Cursor

DECLARE @T TABLE( Marks INT)
Insert into @T values ( 12),(13),(15),(85);

DECLARE @Marks int
Declare @sum int
Declare @i int

Set @sum= 0
Set @i=0

DECLARE MYCURSOR CURSOR FOR
Select * from @T

open Mycursor
fetch next from MYCURSOR into @Marks

while (@@FETCH_STATUS=0)
Begin
Set @sum = @sum+@Marks
Set @i=@i+1
fetch next from MYCURSOR into @Marks
end
Print @sum
Print @sum/@i

Close Mycursor
Deallocate mycursor

Regards
Anna

7

There's a difficulty using the cursor method to accumulate values: nulls. If you substitute a null for one or more rows in the table, the @sum variable becomes NULL and the addition is lost, as well as the division by @i.

If you substitute zero (0) for the nulls, you're skewing the sum and therefore the average. By using the AVG(), SUM(), and COUNT() functions, they will ignore nulls properly and render correct calculations.

You can use the following to compare:

select sum(marks) sum_marks, avg(marks) avg_marks, count(marks) count_marks, count(*) count_all 
, sum(case when marks is null then 1 else 0 end) count_nulls
from @t

If you replace one of the values with NULL you'll see the difference.

8

Will an If statement for NULL help , since we cannot use aggregate function as per the Question Asked..

9

Yes it would fix it.

My original statement was that AVG() was the easiest and best way to accomplish the task. The original question strikes me as silly, I can't think of a valid reason to ask it or want an alternative to AVG().

10

I guess I'm silly, I ask a similar q when testing job applicants to see if they (1) really understand what an AVG is and (2) can work out how to do the equivalent with standard functions:

SELECT SUM(value) / COUNT(value) AS [AVG(value0)]

Easy. Of course if you need to, cast value to decimal / float in the SUM, so that you get decimals in the AVG.

11

Thanks it works