Search code examples
sqldateaggregate-functionsansi-sql

SQL: aggregate functions with DATE type columns

I stumbled upon a question (in a test) about which aggregate functions are applicable to DATE type columns. So, as I understand it, COUNT will just count the number of rows, and MIN and MAX return the earliest/latest date. However, I'm a bit confused about SUM and AVG functions. Will they just convert the DATE values to ints and calculate sum/avg on those ints? Or am I wrong here? Anyway, is this behaviour consistent across all implementations of SQL? Thanks in advance.

Solution

  • In MS SQL Server You cannot call the SUM operator on datetime types, nor can you call the AVG operator.

    MSDN lists the return types of the SUM operator here:
    http://msdn.microsoft.com/en-us/library/ms187810.aspx

    It is also a valid reference for the types on which you may invoke the SUM operator.

    EDIT: In response to your comment, you may use a site like sqlfiddle to test various implementations

    http://www.sqlfiddle.com/#!3/22cee/1

    Considering the backend storage of datetime is not standardized, I contend that it should not be depended upon for any database to return SUM or AVG results in predictable ways.... better not to do it at all...