PostgreSQL: Getting the sum of the difference in dates from two separate tables

Question

The following code:

SELECT ets.event_id, ets.event_date, dtn.don_date, dtn.don_date - ets.event_date AS date_diff FROM events ets, donation dtn
GROUP BY ets.event_id, ets.event_date, dtn.don_date
Order BY ets.event_id;

Yields this result:

My goal is to get the average duration between event_date and don_date per event_id but I cannot seem to find out how.

Answer 1

Apply avg() to the calculation and group by only event_id:

SELECT
  ets.event_id,
  avg(dtn.don_date - ets.event_date) AS avg_date_diff
FROM events ets
CROSS JOIN donation dtn
GROUP BY ets.event_id
ORDER BY ets.event_id

---

CROSS JOIN only used here because that was what the query in the question is doing, however I doubt this is actually wanted.

More likely, a join is required. Perhaps something like:

SELECT
  ets.event_id,
  avg(dtn.don_date - ets.event_date) AS avg_date_diff
FROM donation dtn
JOIN events ets on ets.donation_id = dtn.id
GROUP BY ets.event_id
ORDER BY ets.event_id

But column names for the join were not included in the question.

Answer 2

You need to be able to tie the donation to the event in some way. As it appears you are looking for a single aggregate value then I don't believe any grouping will be involved:

select avg(d.don_date - e.event_date) as avg_days
from events e inner join donation d on d.event_id = e.event_id