From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jan Danielsson <jan(dot)danielsson(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Potentially annoying question about date ranges |
Date: | 2006-09-30 19:16:56 |
Message-ID: | 15846.1159643816@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2006-09-30 20:49:08 | Re: alter integer field to serial integer primary key |
Previous Message | Frank Bax | 2006-09-30 18:43:56 | Re: Potentially annoying question about date ranges |
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Danielsson | 2006-10-01 10:36:05 | Re: Potentially annoying question about date ranges |
Previous Message | Frank Bax | 2006-09-30 18:43:56 | Re: Potentially annoying question about date ranges |