I need to find the average number of days between
date_b, across all rows.
The average number of days between
date_b for this set is 817.3
There are around 10k rows in the table. Columns
date_b are indexed.
What’s the most efficient way of handling this, in a single query?
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;
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;