SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, May 29, 2006

average question

SQL Apprentice 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


Celko Answers
select avg(1.0*SCORE) AS AVERAGE_SCORE FROM GRADES


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.

No comments: