CODEWITHSUNDEEP
sqlsql-server
user1292656
user1292656

Reputation: 2560

AVG function not working

I am working on SQL Server. I have a table that has an int column HalfTimeAwayGoals and I am trying to get the AVG with this code:

select 
    CAST(AVG(HalfTimeAwayGoals) as decimal(4,2)) 
from 
    testtable 
where 
    AwayTeam = 'TeamA'

I get as a result 0.00. But the correct result should be 0.55.

Do you have any idea what is going wrong ?

Upvotes: 0

Views: 1692

Answers (6)

Zaif Ullah
Zaif Ullah

Reputation: 71

cast(avg(cast(LoginCount as decimal(12,2))) as decimal(12,2))

Upvotes: 0

Cyril Joudieh
Cyril Joudieh

Reputation: 124

I used the /1.00 OR /1.0000 trick on various databases to do the job. Unfortunately, I don't have access MS-SQL to try it. The division of integer by 1.00 will change the integer to decimal.

SELECT AVG(integerfield/1.00) FROM table

Upvotes: 0

Cyril Joudieh
Cyril Joudieh

Reputation: 124

Can you try dividing by 1.00 to convert the integer into decimal?

select
    AVG(HalfTimeAwayGoals/1.00) as average 
from
    testtable 
where
    AwayTeam = 'TeamA'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If the field HalfTimeAwayGoals is an integer, then the avg function does an integer average. That is, the result is 0 or 1, but cannot be in between.

The solution is to convert the value to a number. I often do this just by multiplying by 1.0:

select CAST(AVG(HalfTimeAwayGoals * 1.0) as decimal(4, 2)) 
from testtable 
where AwayTeam = 'TeamA';

Note that if you do the conversion to a decimal before the average, the result will not necessary have a scale of 4 and a precision of 2.

Upvotes: 0

Cyril Joudieh
Cyril Joudieh

Reputation: 124

In MySQL

SELECT AVG(integerfield) FROM table

gives me 4 decimal points.

If I do

SELECT AVG(integerfield/1.00) FROM table

I get 8 decimal points.

Upvotes: -1

user1292656
user1292656

Reputation: 2560

select 
    AVG(CAST(HalfTimeAwayGoals as decimal(4,2))) 
from 
    testtable 
where 
    AwayTeam = 'TeamA'

Upvotes: 3

Related Questions