I have a table of 5000 records with a date column.
How do I find the average of those dates. I've tried AVG(datefield)
, but it says Operand data type datetime is invalid for avg operator
We have to pass the column name as a parameter. The avg() function has the following syntax: SELECT AVG( column_name ) FROM table_name; The avg() function can be used with the SELECT query for retrieving data from a table.
AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. If the sum exceeds the maximum value for the data type of the return value, AVG() will return an error.
In PostgreSQL, to get the average of multiple (2 to 8) columns in one row just define a set of seven functions called average(). Will produce the average of the non-null columns.
SQL AVG function is used to find out the average of a field in various records. You can take average of various records set using GROUP BY clause. Following example will take average all the records related to a single person and you will have average typed pages by every person.
If you want to average date only:
SELECT CAST(AVG(CAST(datefield AS INT)) AS DATETIME) FROM dates;
And if you want to consider time:
SELECT CAST(AVG(CAST(datefield AS FLOAT)) AS DATETIME) FROM dates;
See fiddle to test.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With