Re: [NOVICE] For each record in SELECT

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.

I hope I've explained clearly my question.

Thanks for the help.

Best Regards.

--
Luis Magaña.
Gnovus Networks & Software.
www.gnovus.com

on 1/31/03 2:21 PM, joe666(at)gnovus(dot)com purportedly said:

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

Getting this kind of result using only a single SQL query is not likely
possible, especially to get the result table you indicate above. In any
case, it would be easier to group by month and description, sum() the
amount, then aggregate the result table in your application.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

On Friday 31 January 2003 14: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.
>

From what I understand in your email you just need to add a GROUP BY clause.
Try a query like this.

SELECT description, sum(amount) FROM table GROUP BY description;

That should do what you need,

Andy

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