pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFor each record in SELECT
4+ messages / 4 participants
[nested] [flat]
* For each record in SELECT
@ 2003-01-31 21:21 Luis Magaña <joe666@gnovus.com>
2003-02-01 01:28 ` Re: For each record in SELECT Keary Suska <hierophant@pcisys.net>
2003-02-01 02:36 ` Re: [SQL] For each record in SELECT Andrew J. Kopciuch <akopciuch@bddf.ca>
2003-02-01 04:43 ` Re: [NOVICE] For each record in SELECT Andrew McMillan <andrew@catalyst.net.nz>
0 siblings, 3 replies; 4+ messages in thread
From: Luis Magaña @ 2003-01-31 21:21 UTC (permalink / raw)
To: PostgreSQL General <pgsql-general@postgresql.org>; PostgreSQL Novice <pgsql-novice@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: For each record in SELECT
2003-01-31 21:21 For each record in SELECT Luis Magaña <joe666@gnovus.com>
@ 2003-02-01 01:28 ` Keary Suska <hierophant@pcisys.net>
2 siblings, 0 replies; 4+ messages in thread
From: Keary Suska @ 2003-02-01 01:28 UTC (permalink / raw)
To: PostgreSQL <pgsql-general@postgresql.org>
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"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] For each record in SELECT
2003-01-31 21:21 For each record in SELECT Luis Magaña <joe666@gnovus.com>
@ 2003-02-01 02:36 ` Andrew J. Kopciuch <akopciuch@bddf.ca>
2 siblings, 0 replies; 4+ messages in thread
From: Andrew J. Kopciuch @ 2003-02-01 02:36 UTC (permalink / raw)
To: joe666@gnovus.com, PostgreSQL General <pgsql-general@postgresql.org>; PostgreSQL Novice <pgsql-novice@postgresql.org>; pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [NOVICE] For each record in SELECT
2003-01-31 21:21 For each record in SELECT Luis Magaña <joe666@gnovus.com>
@ 2003-02-01 04:43 ` Andrew McMillan <andrew@catalyst.net.nz>
2 siblings, 0 replies; 4+ messages in thread
From: Andrew McMillan @ 2003-02-01 04:43 UTC (permalink / raw)
To: joe666@gnovus.com; +Cc: PostgreSQL General <pgsql-general@postgresql.org>; PostgreSQL Novice <pgsql-novice@postgresql.org>; 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/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-02-01 04:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-31 21:21 For each record in SELECT Luis Magaña <joe666@gnovus.com>
2003-02-01 01:28 ` Keary Suska <hierophant@pcisys.net>
2003-02-01 02:36 ` Andrew J. Kopciuch <akopciuch@bddf.ca>
2003-02-01 04:43 ` Andrew McMillan <andrew@catalyst.net.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox