Skip site navigation (1) Skip section navigation (2)

Range

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Range
Date: 2006-10-13 22:17:22
Message-ID: 20061013220423.M38604@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi All,

I am writing a query that UNIONs a bunch of data together.  I would like to add
a query to the UNION that will insure that if nothing else is retrieved a zero
will be available.

The data I am retrieving in each part of the UNION looks something like this
item,quantity,ship_date
A,   10,      2006-01-01
A,    5,      2006-02-01
A,    3,      2006-02-01
A,   20,      2006-04-01

Notice the gap in the date sequence.

How can I generate a zero quantity to fill the gap?  Either in the original
retrieval or as a seperate query for the UNION or some other way so that when I
GROUP BY the date and SUM the quantity I end up with

item,quantity,ship_date
A,   10,      2006-01-01
A,    8,      2006-02-01
A,    0,      2006-03-01
A,   20,      2006-04-01

The queries whose results are being assembled with the UNION all have the form
SELECT item,
       quantity,
       DATE_TRUNC('MONTH', source_table.ship_date)::date AS ship_date
  FROM source_table;

As always hints, suggestions and URLs for applicable documentation will be
appreciate.

Kind Regards,
Keith

Responses

  • Re: Range at 2006-10-13 23:58:11 from Frank Bax

pgsql-novice by date

Next:From: Frank BaxDate: 2006-10-13 23:58:11
Subject: Re: Range
Previous:From: Aaron BonoDate: 2006-10-13 21:45:18
Subject: Re: Copying data from table to table (cloned tables)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group