From: Christopher Crews <isephoenix@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Date and filling issues
Date: Wed, 19 Mar 2008 12:02:25 -0700 (PDT)
Message-ID: <687096.56737.qm@web52202.mail.re2.yahoo.com> (raw)
Hi All,
I'm not quite sure how to phrase this, but essentially my company has me working on some reports and I have some charts associated with the SQL results.
My current query is:
select
transaction_date as date,
sum(sale_amount) as sales
from ej_transaction
where transaction_date
between current_date - Interval '1 month' and current_date
group by transaction_date
order by transaction_date asc
The issue I'm having is that there are some dates where sales of certain items simply don't take place. Instead of putting a date entry in the database with a sale amount of 0, there simply is no entry for that date. I need to make a query that will fill in the dates not found within the date range and populate them with the sales value of 0.
A sample of the current results data would be like
date sales
2008-03-07 100.00
2007-03-10 150.00
2007-03-18 50.00
and what I'm trying to do is fill in the missing dates with sales values of 0.
Thanks,
-CC
---------------------------------
Never miss a thing. Make Yahoo your homepage.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: isephoenix@yahoo.com
Subject: Re: Date and filling issues
In-Reply-To: <687096.56737.qm@web52202.mail.re2.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox