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.

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(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

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.

I have a similar question,

I receive payments, but not every day (or even every week),
and I would like see how much I receive per week and/or per
month. I also need to compare different years.

I also would like to see what the (accumulated) sum of the
payments is at the end of a given week, month.

And then of course I would like to show this to someone, ie
make a graph. X-Axis 12 months (or 52 weeks). Left X-Axis the amounts
for the bars which should be in different colors (per month/bar), right
Y-Axis the amounts for the totals as a line (so that the bars don't
become too flat).

If such a combined graph is not possible/feasible, I could live with
one plot each per year.

I got the usual tools on the Mac, including R, gnuplot and fink.

any help would be appreciated.

el

on 9/30/06 8:34 PM Jan Danielsson said the following:

> 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.

--
If you want to email me, replace nospam with el

Eberhard Lisse wrote:
> I receive payments, but not every day (or even every week),
> and I would like see how much I receive per week and/or per month. I
> also need to compare different years.
>
> I also would like to see what the (accumulated) sum of the payments
> is at the end of a given week, month.

Timestamp each transaction, then use a GROUP BY clause to aggregate the
numbers however you wish. If the available date field extractions are
not sufficient, write a scalar function of your own to accomplish what
you need.

--
Guy Rouillier

Thank you very much.

Sorry to be unclear, they are time stamped, and I wondered if someone
could give me ssome advise how I do this aggregation. In particular do
I not know how to write such a scalar function.

Anything re-usable lying around as examples somewhere?

el

on 10/4/06 9:46 PM Guy Rouillier said the following:
> Eberhard Lisse wrote:
>> I receive payments, but not every day (or even every week),
>> and I would like see how much I receive per week and/or per month. I
>> also need to compare different years.
>>
>> I also would like to see what the (accumulated) sum of the payments
>> is at the end of a given week, month.
>
> Timestamp each transaction, then use a GROUP BY clause to aggregate the
> numbers however you wish. If the available date field extractions are
> not sufficient, write a scalar function of your own to accomplish what
> you need.
>

--
If you want to email me, replace nospam with el

Ok, Ok, Ok,

Practical PostgreSQL, Chapter 5, date_part(), to_char()

I got it :-)-O

el

on 10/8/06 9:43 AM Eberhard Lisse said the following:
> Thank you very much.
>
> Sorry to be unclear, they are time stamped, and I wondered if someone
> could give me ssome advise how I do this aggregation. In particular do
> I not know how to write such a scalar function.
>
> Anything re-usable lying around as examples somewhere?
>
> el
>
> on 10/4/06 9:46 PM Guy Rouillier said the following:
>> Eberhard Lisse wrote:
>>> I receive payments, but not every day (or even every week),
>>> and I would like see how much I receive per week and/or per month. I
>>> also need to compare different years.
>>>
>>> I also would like to see what the (accumulated) sum of the payments
>>> is at the end of a given week, month.
>> Timestamp each transaction, then use a GROUP BY clause to aggregate the
>> numbers however you wish. If the available date field extractions are
>> not sufficient, write a scalar function of your own to accomplish what
>> you need.
>>
>

--
If you want to email me, replace nospam with el