SQL: aggregate functions with DATE type columns

4
votes

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.

1
sqldateaggregate-functionsansi-sql
"COUNT will just count the number of rows". Actually that's not quite correct. You're right that COUNT(*) counts the number of rows, but COUNT(somedatetypecolumn) counts the number of rows where somedatetypecolumn is not NULL. - Mark Byers
Oh, right, I wasn't explicit enough there. - sdds

1 Answers

5
votes

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...