From: Andrew McMillan <andrew@catalyst.net.nz>
To: joe666@gnovus.com
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Cc: PostgreSQL Novice <pgsql-novice@postgresql.org>
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: [NOVICE] For each record in SELECT
Date: 01 Feb 2003 17:43:48 +1300
Message-ID: <1044074628.26387.12.camel@kant.mcmillan.net.nz> (raw)
In-Reply-To: <1044048077.1380.3.camel@kerberus>
References: <1044048077.1380.3.camel@kerberus>
On Sat, 2003-02-01 at 10:21, Luis Magaña wrote:
> Hi,
>
> I have a question here:
>
> I have a table with this fields:
>
> month
> description
> amount
>
> now I have to write a query that retrieves the sum of the amount from
> the minimum month to the maximum month registered for each diferent
> description.
>
> Of course there are cases when a particular description has not record
> for all the months in that period. I mean, suppouse you have this
> records:
>
> month description amount
> -----------------------------------------------
> June description1 100
> July description1 500
> August description1 600
> June description2 300
> August description2 400
>
> how you write a query that outputs something like this:
>
> June July August
> ------------------------------------------
> description1 | 100 500 600
> description2 | 300 0 400
>
> My problem is for the 0 value.
If you have another table with columns like:
month description
--------------------
June description1
July description1
August description1
June description2
July description2
August description2
Then you will be able to do an outer join to it like:
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.month = t2.month AND
t1.description = t2.description
This will give you a NULL, rather than a zero, but your application
should be able to handle that detail.
You can also do this having two tables: one for the months, and another
for the descriptions:
SELECT * FROM months m CROSS JOIN descriptions d FULL OUTER JOIN values
v ON m.month = v.month AND d.description = v.description
I hope this is some use,
Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------
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: andrew@catalyst.net.nz, joe666@gnovus.com, pgsql-general@postgresql.org, pgsql-novice@postgresql.org
Subject: Re: [NOVICE] For each record in SELECT
In-Reply-To: <1044074628.26387.12.camel@kant.mcmillan.net.nz>
* 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