DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=NGJbykr2KPSD9WT/YSZdOsVewTbqZCY0TbUN+neZCskOg/gFqHkibg1Cx5ljQl+HubRjzF5a4EXCcPIkt/vTyuMOSHmDt3CjGRqs9oct4YdwFTznIIzAJyRkhlylf2C+GhOkzYkkoboqq+aSytHPuHGPM1fCSc3uM84bTnb6pE0=
Message-ID: <690707f60803191936w4581b8dj75d49837be1c1892@mail.gmail.com>
Date: Wed, 19 Mar 2008 23:36:08 -0300
From: "Osvaldo Kussama" <osvaldo.kussama@gmail.com>
To: "Christopher Crews" <isephoenix@yahoo.com>
Subject: Re: Date and filling issues
Cc: pgsql-sql@postgresql.org
In-Reply-To: <687096.56737.qm@web52202.mail.re2.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <687096.56737.qm@web52202.mail.re2.yahoo.com>

2008/3/19, Christopher Crews <isephoenix@yahoo.com>:
> 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.
>

Try:
SELECT s.date::date, sum(COALESCE(ej_transaction.sale_amount,0)) as sales
FROM generate_series(current_date - '1 month', current_date) AS s(date)
     LEFT OUTER JOIN ej_transaction
GROUP BY s.date
ORDER BY s.date ASC;

Osvaldo