Re: [NOVICE] For each record in SELECT

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