I need to find the average number of days between date_a and date_b, across all rows.

The average number of days between date_a and date_b for this set is 817.3

There are around 10k rows in the table. Columns date_a and date_b are indexed.

What's the most efficient way of handling this, in a single query?

CodePudding user response：

First of all you can use DATEDIFF() function to calculate difference between two days and then AVG() function to get average of differences:

SELECT AVG(DATEDIFF(date_b, date_a)) as average_days FROM table_name;

UPDATE:

One more way is to sum up all differences and then divide by count of all rows:

SELECT SUM(DATEDIFF(date_b, date_a))/COUNT(*) as average_days FROM table_name;