Average Function

Have you ever tried something like this:

SELECT AVG(NUM)
FROM
(
    SELECT 1 AS NUM UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 0

) AS A

Are you expecting an answerr of 0.75? You will be surprised to see the return value is only 0.
The reason behind this is how average function works.

If the input values are of integer data type, the average function too will return an average value.
So if you have a column which has integer data type, you need to convert it to decimal before calculating the average value

SELECT AVG(NUM*1.0)
FROM
(
    SELECT 1 AS NUM UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 0

) AS A


Are you happy now?