Subject: Re: [NOVICE] For each record in SELECT
From: Andrew McMillan <andrew@catalyst.net.nz>
To: joe666@gnovus.com
Cc: PostgreSQL General <pgsql-general@postgresql.org>,
	PostgreSQL Novice <pgsql-novice@postgresql.org>,
	PostgreSQL SQL <pgsql-sql@postgresql.org>
In-Reply-To: <1044048077.1380.3.camel@kerberus>
References: <1044048077.1380.3.camel@kerberus>
Content-Type: text/plain; charset=UTF-8
Organization: 
Message-Id: <1044074628.26387.12.camel@kant.mcmillan.net.nz>
Mime-Version: 1.0
Date: 01 Feb 2003 17:43:48 +1300
Content-Transfer-Encoding: quoted-printable

On Sat, 2003-02-01 at 10:21, Luis Maga=C3=B1a wrote:
> Hi,
>=20
> I have a question here:
>=20
> I have a table with this fields:
>=20
> month
> description
> amount
>=20
> 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.
>=20
> 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:
>=20
> month	description	amount
> -----------------------------------------------
> June	description1	100=09
> July	description1	500
> August	description1	600
> June	description2	300
> August	description2	400
>=20
> how you write a query that outputs something like this:
>=20
>             	June	July	August
> ------------------------------------------
> description1 |	100	500	600
> description2 |	300	0	400
>=20
> 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 =3D t2.month AND
t1.description =3D 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 =3D v.month AND d.description =3D v.description

I hope this is some use,
					Andrew.
--=20
---------------------------------------------------------------------
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/=20
---------------------------------------------------------------------