Jan Danielsson <jan.danielsson@gmail.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
