Re: [NOVICE] For each record in SELECT

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: joe666(at)gnovus(dot)com
Cc: PostgreSQL General <pgsql-general(at)postgresql(dot)org>, PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>, PostgreSQL SQL <pgsql-sql(at)postgresql(dot)org>
Subject: Re: [NOVICE] For each record in SELECT
Date: 2003-02-01 04:43:48
Message-ID: 1044074628.26387.12.camel@kant.mcmillan.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice pgsql-sql

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/
---------------------------------------------------------------------

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Curt Sampson 2003-02-01 05:07:00 Re: One large v. many small
Previous Message will trillich 2003-02-01 04:38:09 Re: create view ... select fld,'constant',fld ...

Browse pgsql-novice by date

  From Date Subject
Next Message Bruno Wolff III 2003-02-01 06:43:29 Re: can only connect to localhost is postgresql
Previous Message Michelle Konzack 2003-02-01 03:32:46 Re: Good books?

Browse pgsql-sql by date

  From Date Subject
Next Message Rajesh Kumar Mallah. 2003-02-01 05:03:43 Re: Which version is this?
Previous Message Tom Lane 2003-02-01 04:43:22 Re: Controlling access to Sequences