## 6 replies

Last post Apr 28, 2015 08:46 AM by ricas

• ricas

Member

41 Points

410 Posts

### Calculating average value

Hi all,

In a table ‘Gradings’ that contains training records, a data field ‘OverallGrade’ (datatype: int, NULL is allowed) stores a number (the grading).

I need to calculate an average number from the OverallGrade. My problem is that in some of the records the OverallGrade is a NULL, so simply dividing the total of all grades with the total number of records does not return a correct number.

So how do I omit the records with NULL values in this field, so only records that actually contains a value in this field is counted / in the calculation ?

Thanks

_________________________________________________

..... I am new to all this, so please be patient
• gimimex

Contributor

5450 Points

1123 Posts

### Re: Calculating average value

Hi,

You are using the AVG function?

`select AVG(OverallGrade) as OverallGrade from MyTable`

Hope this helps.

• ricas

Member

41 Points

410 Posts

### Re: Calculating average value

Hi,

Right you are - I was under the impression that NULL values would be counted as 0, but that does not seems to be so.

Thanks a lot !

_________________________________________________

..... I am new to all this, so please be patient
• Edwin Guru S...

Star

8536 Points

1376 Posts

### Re: Calculating average value

Apr 27, 2015 05:10 AM|Edwin Guru Singh|LINK

#### ricas

So how do I omit the records with NULL values in this field, so only records that actually contains a value in this field is counted / in the calculation ?

As per this case, the following example may give more explanations :

```create table stable
(name varchar(50) ,
mark int )
------------------------
insert into stable(name,mark) values ('A',25),('B',30),('C',15),('D',30),('E',NULL);
------------------------
select * from stable
select AVG(mark) [OverallGrade] from stable  --By default ,it ommit NULL values
select AVG(ISNULL(mark,0)) [OverallGrade] from stable --Use ISNULL() if you need NULL values for calculate average
------------------------
drop table stable```

--
with regards,
Edwin

with regards,
Edwin
• ricas

Member

41 Points

410 Posts

### Re: Calculating average value

Thanks Edwin, your example was very useful for me to better understand the AVG function.

But how do I get a decimal from AVG ? I only get a rounded number ?

brgds

_________________________________________________

..... I am new to all this, so please be patient
• Edwin Guru S...

Star

8536 Points

1376 Posts

### Re: Calculating average value

Apr 27, 2015 09:39 PM|Edwin Guru Singh|LINK

#### ricas

how do I get a decimal from AVG ? I only get a rounded number ?

As per this case, you can refer the following example :

```create table stable
(name varchar(50) ,
amount decimal(12,5) )
------------------------
insert into stable(name,amount) values ('A',25.103),('B',30.242),('C',15.67),('D',30.19237),('E',NULL);
------------------------
select * from stable
select AVG(amount) [Average] from stable  --By default ,it ommit NULL values
select AVG(ISNULL(amount,0)) [Average] from stable --Use ISNULL() if need NULL values for calculate average

-----Round-off in two decimal points----
select CAST(AVG(amount) as NUMERIC(12,2)) [Average] from stable  --By default ,it ommit NULL values
select CAST(AVG(ISNULL(amount,0)) as NUMERIC(12,2)) [Average] from stable --Use ISNULL() if need NULL values for calculate average
------------------------
drop table stable```

--
with regards,
Edwin

with regards,
Edwin
• ricas

Member

41 Points

410 Posts