SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot auth1's Avatar
    Join Date
    Nov 2004
    Location
    Melbourne, Australia
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    AVG returning NULL - temporal values?

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    nothing wrong with your query

    perhaps there were no rows that satisfied the criteria

    add COUNT(*) to the SELECT clause to confirm
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •