average question

SELECT AVG(SCORE) AS AVERAGE_SCORE
FROM GRADES
SCORE is an integar column with values from 0 - 5
Now this only returns integer values such as 3, 4...
How can I make AVERAGE_SCORE to be a decimal?
ie 3.452
Howardselect avg(1.0*SCORE) AS AVERAGE_SCORE FROM GRADES
Without the 1.0*, the average will be SUM(SCORE)/COUNT(SCORE),
which will be a quotient of integers, and use integer division which
discards any remainder.
Steve Kass
Drew University
"Howard" <howdy0909@.yahoo.com> wrote in message
news:uxcoNcggGHA.3996@.TK2MSFTNGP03.phx.gbl...
> SELECT AVG(SCORE) AS AVERAGE_SCORE
> FROM GRADES
> SCORE is an integar column with values from 0 - 5
> Now this only returns integer values such as 3, 4...
> How can I make AVERAGE_SCORE to be a decimal?
> ie 3.452
>
> Howard
>|||Thanks Steve
One more question
Is it possible to update the value of the field CLASS_AVG in the same query?
I tried this but it didn't work
UPDATE RESULTS
SET CLASS_AVG = AVERAGE_SCORE IN
(SELECT AVG(SCORE) AS AVERAGE_SCORE
FROM GRADES)
"Steve Kass" <skass@.drew.edu> wrote in message
news:u99HpjggGHA.1264@.TK2MSFTNGP05.phx.gbl...
> select avg(1.0*SCORE) AS AVERAGE_SCORE FROM GRADES
> Without the 1.0*, the average will be SUM(SCORE)/COUNT(SCORE),
> which will be a quotient of integers, and use integer division which
> discards any remainder.
> Steve Kass
> Drew University
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:uxcoNcggGHA.3996@.TK2MSFTNGP03.phx.gbl...
>|||If you are sure of the functioanltiy then may be you should try this.
UPDATE RESULTS
SET CLASS_AVG = (SELECT AVG(SCORE)
FROM GRADES)
But remember this will update the class_avg with the average that you
caclulate for all the rows in the table.|||Howard (howdy0909@.yahoo.com) writes:
> One more question
> Is it possible to update the value of the field CLASS_AVG in the same
> query?
> I tried this but it didn't work
> UPDATE RESULTS
> SET CLASS_AVG = AVERAGE_SCORE IN
> (SELECT AVG(SCORE) AS AVERAGE_SCORE
> FROM GRADES)
You can say simply:
UPDATE RESULTS
SET CLASS_AVG = (SELECT AVG(SCORE) AS AVERAGE_SCORE FROM GRADES)
But this would update every row in RESULTS with the same value,
which may not be what you want.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just put the query in a VIEW and it will be re-calculated each time.
You are still thinking like a COBOL programmer who wants to write all
his data to a file, not a like an SQL programmer who knows that a VIEW
is also a TABLE and does not have to havea physical existence.|||Or use
select cast(score as decimal (3,2)) as average_score
Same effect, but the explicit cast may be clearer to the next person who
review the code. The next programmer may not know that 1.0*score actually
converts it to a decimal from an int. I never understood it until I started
following this newsgroup.
"Steve Kass" <skass@.drew.edu> wrote in message
news:u99HpjggGHA.1264@.TK2MSFTNGP05.phx.gbl...
> select avg(1.0*SCORE) AS AVERAGE_SCORE FROM GRADES
> Without the 1.0*, the average will be SUM(SCORE)/COUNT(SCORE),
> which will be a quotient of integers, and use integer division which
> discards any remainder.
> Steve Kass
> Drew University
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:uxcoNcggGHA.3996@.TK2MSFTNGP03.phx.gbl...
>