Unexpected result using AVG() on negative floats in mySQL

Question

This query

SELECT * 
FROM `rounds` 
WHERE `user` = 18956 AND `handicapDifferential` IS NOT NULL 
ORDER BY `handicapDifferential` DESC, `date` DESC 
LIMIT 0, 2

Gives expected result of two rows with -1.4 and -5.1 in handicapDifferential. The field datatype is FLOAT.

Using AVG() on the same result with this query

SELECT AVG(`handicapDifferential`) as `avg` 
FROM `rounds` 
WHERE `user` = 18956 AND `handicapDifferential` IS NOT NULL 
ORDER BY `handicapDifferential` DESC, `date` DESC 
LIMIT 0, 2

Gives -9.485714214188713 where I expect -3.25.

Here is a fiddle demonstrating the result and the issue: http://sqlfiddle.com/#!9/32acd/3

What am I doing incorrectly here?

I think the not null clause is returning some extra rows...also group by before count and avg is always a good thing to do — Rohit Kumar, Nov 13 '17 at 19:23
Thank you for the reply. Both queries use LIMIT 0,2 and both have the IS NOT NULL. Only difference is one selects all fields and other does AVG() on just one. How can the second query return different rows? — jerrygarciuh, Nov 13 '17 at 19:25
what is the type of handicapDifferential ?? string , double etc — Rohit Kumar, Nov 13 '17 at 19:28

Mohammedshafeek C S · Accepted Answer · 2017-11-13T19:52:48.720

1

Alter your query to

SELECT 
  AVG(items.avg) 
FROM
  (SELECT 
    `handicapDifferential` AS `avg` 
  FROM
    `rounds` 
  WHERE `user` = 18956 
    AND `handicapDifferential` IS NOT NULL 
  ORDER BY `handicapDifferential` DESC,
    `date` DESC 
  LIMIT 0, 2) items

refer link

Explanation : AVG is a an aggregate keyword in MySQL and you should have given a result set as the input as per your requirement. But u called AVG as a member in your query.

edited Nov 13 '17 at 19:52

answered Nov 13 '17 at 19:29

Mohammedshafeek C S

1,916
2
16
26

Please explain what the problem is and how this solves it. – Barmar Nov 13 '17 at 19:47
is that the hard explanation for that the function AVG counted all records as group instead off the correct group records 1 and 2? – Raymond Nijland Nov 13 '17 at 20:03
The limit is applied *after* the AVG, therefore the AVG is applied to all rows matching the WHERE condition. LIMIT in this case does nothing as the query returns just one row. – slaakso Nov 13 '17 at 20:23

Unexpected result using AVG() on negative floats in mySQL

1 Answers1