AVG() of the quotient of two fields giving unexpected output in SQL

I have a SQL table and I need to find the average of the quotient of two fields (rating, position) grouped by another field (query_name). I cannot make sense of why the following two queries don’t give the same result:

Query 1:

SELECT query_name, AVG(rating/position) AS quality
FROM queries
GROUP BY query_name

Query 2:

SELECT query_name, AVG(rating*100/position)*100 AS quality
FROM queries
GROUP BY query_name

Here is the table:

+------------+-------------------+----------+--------+
| query_name | result            | position | rating |
+------------+-------------------+----------+--------+
| Dog        | Golden Retriever  | 1        | 5      |
| Dog        | German Shepherd   | 2        | 5      |
| Dog        | Mule              | 200      | 1      |
| Cat        | Shirazi           | 5        | 2      |
| Cat        | Siamese           | 3        | 3      |
| Cat        | Sphynx            | 7        | 4      |
+------------+-------------------+----------+--------+

This is the expected output:

Result table:
+------------+---------+
| query_name | quality |
+------------+---------+
| Dog        | 2.50    |
| Cat        | 0.66    |
+------------+---------+

However, Query 1 gives the following:

Result table:
+------------+---------+
| query_name | quality |
+------------+---------+
| Dog        | 2.33    |
| Cat        | 0.33    |
+------------+---------+

Answer

Some databases do integer division, so 1/2 is 0 rather than 0.5. If this is the case, then your numbers will be off.

It is easily fixed. I just multiply by 1.0:

SELECT query_name, AVG(rating * 1.0 / position) AS quality

Advertisement

Answer