Thread: Potentially annoying question about date ranges
Hi, I'm going to assume that this question has been asked a gazillion times, and is in every SQL book known to man and aliens. And I also assume it is in the FAQ. But I'm going to ask anyway. I have a database in which I store my purchases -- mainly for entertainment (you have no idea how dull my life is). In an effort to get some variation in my life, I thought I'd find out how much of my valuable money I waste each day - on average. "select avg(sum) ... group by date" would produce a result, which isn't correct -- it would show me how much I spend in average those days I actually do spend anything, not counting those wonderful days when I don't spend any money at all (and since I'm such a cheap skate, that happens a lot). The table I use basically looks like this (well, the relevant parts, anyway): table transaction ( id serial, numeric(8,2) dt date, description text ) Now, I get the problem. In my query, I group by date, and then take the average of the sums. The problem is that all dates which I haven't wasted any money should be listed with the sum 0 (such a wonderful number, when it comes to expenses). But it obviously isn't. 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. (I want to look at that number every morning, and if by the end of the day I haven't managed to keep my expenses under that number, I whip myself ten times and cry myself to sleep). I assume that I have to somehow select a date-range, and somehow merge it with the transaction table ... some way. Can it be done? (I'm not asking for a working solution, just some hints or tips). Do I use unions? -- Kind Regards, Jan Danielsson Te audire non possum. Musa sapientum fixa est in aure.
Attachment
At 02:34 PM 9/30/06, Jan Danielsson wrote: > 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. (I want to look at that number every morning, >and if by the end of the day I haven't managed to keep my expenses under >that number, I whip myself ten times and cry myself to sleep). http://www.postgresql.org/docs/8.1/static/functions-srf.html
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
Jan Danielsson wrote: [---] > In an effort to get some variation in my life, I thought I'd find out > how much of my valuable money I waste each day - on average. [---] Many thanks to all tho replied. I have learned much, and learned about database functions. -- Kind Regards, Jan Danielsson Te audire non possum. Musa sapientum fixa est in aure.