From: | Jan Danielsson <jan(dot)danielsson(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Potentially annoying question about date ranges |
Date: | 2006-09-30 18:34:05 |
Message-ID: | 451EB89D.2010301@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Bax | 2006-09-30 18:43:56 | Re: Potentially annoying question about date ranges |
Previous Message | Tom Lane | 2006-09-30 16:47:49 | Re: Dynamic access to record columns (in PL/pgsql) |
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Bax | 2006-09-30 18:43:56 | Re: Potentially annoying question about date ranges |
Previous Message | Bruno Wolff III | 2006-09-30 02:41:17 | Re: Do non-sequential primary keys slow performance significantly?? |