Re: Potentially annoying question about date ranges

Jan Danielsson <jan(dot)danielsson(at)gmail(dot)com> writes:
> Is there a painfully obvious way (that I'm missing) to get all dates
> included in a query, using my current table design, so that my average
> will tell me how much I spend per day on average - including those days
> I don't spend anything.

Not sure about pure-SQL-standard ways, but the way I'd do it in PG is to
generate all the dates in the desired range using generate_series,
say like this:

select avg(dayspend) from
(select sum(coalesce(amt,0)) as dayspend from
(select '2006-01-01'::date + n as dt
from generate_series(0,'2006-12-31'::date-'2006-01-01'::date) as n) as days
left join transaction on (transaction.dt = days.dt)
group by days.dt) as ss;

You could make it a little cleaner by creating a function:

create function generate_dates(start date, stop date) returns setof date
as $$select $1 + n from generate_series(0, $2-$1) as n$$ language sql strict;

select avg(dayspend) from
(select sum(coalesce(amt,0)) as dayspend from
(select generate_dates('2006-01-01','2006-12-31') as dt) as days
left join transaction on (transaction.dt = days.dt)
group by days.dt) as ss;

regards, tom lane