Joe Celko's SQL for Smarties, 3rd Edition

23.2. The AVG() Function

One problem is that SQLs likes to maintain the data types, so if x is an INTEGER, you may get an integer result. You can avoid this by writing AVG(1.0 * x) or AVG(CAST (X AS FLOAT)) or AVG(CAST (X AS DECIMAL (s,p))) to be safe. This is implementation-defined, so check your product first.

Newbies tend to forget that the built-in aggregate functions drop the rows with NULLs before doing the computations. This means that (SUM(x)/COUNT(*)) is not the same as AVG(x). Consider (x * 1.0)/COUNT(*) versus AVG(COALESCE(x * 1.0, 0.0)) as versions of the mean that handle NULLs differently.

Sample and population means are slightly different. A sample needs to use frequencies to adjust the estimate of the mean. The formula SUM(x * ...