From: Osvaldo Kussama <osvaldo.kussama@gmail.com>
To: Christopher Crews <isephoenix@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Date and filling issues
Date: Wed, 19 Mar 2008 23:36:08 -0300
Message-ID: <690707f60803191936w4581b8dj75d49837be1c1892@mail.gmail.com> (raw)
In-Reply-To: <687096.56737.qm@web52202.mail.re2.yahoo.com>
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
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: osvaldo.kussama@gmail.com, isephoenix@yahoo.com
Subject: Re: Date and filling issues
In-Reply-To: <690707f60803191936w4581b8dj75d49837be1c1892@mail.gmail.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