Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 532612E002F for ; Wed, 19 Mar 2008 23:09:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 55044-01 for ; Wed, 19 Mar 2008 23:09:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web52202.mail.re2.yahoo.com (web52202.mail.re2.yahoo.com [206.190.48.125]) by postgresql.org (Postfix) with SMTP id A4E7D2E005A for ; Wed, 19 Mar 2008 23:09:06 -0300 (ADT) Received: (qmail 58906 invoked by uid 60001); 19 Mar 2008 19:02:25 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=cw+6/vjxKr7E9w9Mbm5U9qmbk8dMbA1F7t198Q34CY02wMQyreVPbAnRB+7q35q5Ty5LyXM8j4YQzMAmikpwW6C2E/ZQEPk97fldDWN0C4JPUo+yhfuE/mi3dVGLqwVnyH1Dm68sEHa92MrfbveJB9j77Gzh3cfmP7fRwV5SjaE=; X-YMail-OSG: 06uN_o0VM1nfb0wp8KalT0f.eQ.MWcaRi.EEtwx_RyWkJFzpDRJsq.6mIa2s4Vya0hjMx9b2FJAdC6rx3CLOMKrlR5KAMjbeWNAQAjED_3f8_k5go_EOztg3mrEm0rsomJ0ysyvEPoslwPA- Received: from [67.63.160.178] by web52202.mail.re2.yahoo.com via HTTP; Wed, 19 Mar 2008 12:02:25 PDT Date: Wed, 19 Mar 2008 12:02:25 -0700 (PDT) From: Christopher Crews Subject: Date and filling issues To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1609079130-1205953345=:56737" Content-Transfer-Encoding: 8bit Message-ID: <687096.56737.qm@web52202.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/126 X-Sequence-Number: 30454 --0-1609079130-1205953345=:56737 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-1609079130-1205953345=:56737 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-1609079130-1205953345=:56737--