Thread: For each record in SELECT

For each record in SELECT

From

Luis Magaña

Date:

31 January 2003, 21:25:32

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

Re: For each record in SELECT

From

Keary Suska

Date:

01 February 2003, 00:14:45

on 1/31/03 2:21 PM, joe666@gnovus.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"

Re: [SQL] For each record in SELECT

From

"Andrew J. Kopciuch"

Date:

01 February 2003, 01:39:57

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

Re: [NOVICE] For each record in SELECT

From

Andrew McMillan

Date:

01 February 2003, 02:46:04

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