Search code examples
mysqlsqlrdbms

MySQL seems to have a bug with distinct, avg, and group-by

The following query seems to behave incorrectly. With a group by on a single field and an average on that field distinct avg should probably return one row for every distinct average. If avg is replaced by count for example the query would return two rows and if distinct is removed or replaced by all then the query returns three rows.

CREATE TABLE x (x real);
INSERT INTO x VALUES (1), (2), (3), (3);
SELECT DISTINCT AVG(x) FROM x GROUP BY x;

MySQL returns:

+--------+
| AVG(x) |
+--------+
|      1 |
+--------+

PostGres returns:

 AVG
-----
   1
   2
   3
Solution

  • Below query return this output : 

     AVG
-----
   1
   2
   3

    Query

    SELECT  AVG(x) FROM x 
GROUP BY x;

    Here is the SQL fiddle

    Demo