AVG returning NULL - temporal values?

Databases
1

I have a MySQL query that works in 5.1 but not 5.5:

SELECT AVG(sales) FROM table WHERE name = ‘customer1’ AND date > DATE_SUB(NOW(), INTERVAL 1 WEEK)

I had a quick look on the reference manual and it appears to be due to a change of how it handles the date field, specifically:

“The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.) To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value.”

I understand that if I were to apply AVG() to the date, for example, I would need to convert the date field. But what do I do in this situation?

2

nothing wrong with your query

perhaps there were no rows that satisfied the criteria

add COUNT(*) to the SELECT clause to confirm