#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171

    SELECTing AVG(DATE) from a table


    Hello;

    There is a table with a column type DATE. Values there are as expected for example: 2014-12-12

    What is the corect way to get the average of the dates? Obvously this is not the right approach
    Code:
    SELECT Avg(date)
    FROM   allotments_new
    WHERE  date > '2004-12-12'
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    please explain what you mean by "average date"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171
    Hi Rudy how are you?

    1 -
    Originally Posted by r937
    please explain what you mean by "average date"
    Lets say there are 1 million entries each have a submition date. Could be anything from 1990 up to 2012.

    I want to find out what year had the most submitions. Please note that the date formats are 'yyyy-mm-dd'.

    2 - Also I am having a strange experince! Adding index on a DATE column made things much slower than usual!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    I want to find out what year had the most submitions.
    this is not the same as "average date"
    Code:
    SELECT YEAR(submissiondate) AS theyear
      FROM thetable
    GROUP
        BY theyear
    ORDER
        BY COUNT(*) DESC
    LIMIT 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171
    The lesson: Get creative.

IMN logo majestic logo threadwatch logo seochat tools logo