I read your response to this question: Calculating AVG date. I am having problems trying to put this in the correct syntax. I have a begin_date and end_date range and I want to get the average of the (completed_date - assigned_date) so will it be
SELECT BETWEEN (begin_date) AND (end_date) AVG (completed_date - assigned_date) ???
I am trying to get the average completion time between the two dates.
The
Requires Free Membership to View
earlier column pointed out that AVG only works on numbers, and showed a technique for mapping
DATEs to numbers so you could use AVG. However, in this case, we are averaging numbers.
(completed_date - assigned_date) is the number of days between assigned_date and completed_date.
The technique for averaging numbers is the same whether the numbers quantify days, weeks, dollars
or apples: simply use the AVG group function.
If your table is called assignment, you can compute the completion time for each row by saying:
SELECT completed_date - assigned_date AS completion_days FROM assignment;
This shows the number of days between assigned_date and completed_date, assuming that neither of the dates is NULL. If either date is NULL (or if both are), then the difference will be NULL also.
To get the average, use the regular AVG group function.
SELECT AVG (completed_date - assigned_date) AS avg_completion_days FROM assignment;
This will compute the average completion time (in days) for all rows where the expression "completed_date - assigned_date" is not NULL, which is probably what you want.
You didn't specify exactly what has to be between begin_date and end_date. The query below requires both completed_date and assigned_date to be in that range. The query also assumes that begin_date and end_date are stored in a one-row table called date_range.
SELECT AVG (completed_date - assigned_date) AS avg_completion_days FROM assignment, date_range WHERE assigned_date BETWEEN begin_date AND end_date AND completed_date BETWEEN begin_date AND end_date;
Since AVG (x - y) = AVG (x) - AVG (y) (assuming NULLs are handled correctly), you could get the same results by using the technique described in the earlier question:
SELECT AVG (completed_date - SYSDATE) - AVG (assigned_date - SYSDATE) AS avg_completion_days FROM assignment, date_range WHERE assigned_date BETWEEN begin_date AND end_date AND completed_date BETWEEN begin_date AND end_date;
but since it's longer to type, harder to understand and slower to run, why would you want
to?
This was first published in November 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation