CODEWITHSUNDEEP
mysqlaveragemean
jerrygarciuh
jerrygarciuh

Reputation: 22018

Unexpected result using AVG() on negative floats in mySQL

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?

Upvotes: 1

Views: 118

Answers (1)

Mohammedshafeek C S
Mohammedshafeek C S

Reputation: 1943

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.

Upvotes: 1

Related Questions