From: | "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com> |
---|---|
To: | "Stefan Schwarzer" <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: "Concatenate" two queries - how? |
Date: | 2007-10-24 13:24:52 |
Message-ID: | D7FF158337303A419CF4A183F48302D6033F844E@hdsmsx411.amr.corp.intel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Use "union" ???
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Stefan
Schwarzer
Sent: Wednesday, October 24, 2007 9:09 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] "Concatenate" two queries - how?
Hi there,
I have two queries, which I would like to bring together to form one
result.
The first query is a simple SELECT on a table of national statistics.
SELECT
COALESCE(c.name, ''''),
year_start AS year,
value
FROM
fish_catch AS d
LEFT JOIN
countries AS c ON c.id = id_country
WHERE
(year_start = 1995 OR year_start = 2000 ) AND
(name = 'Afghanistan' OR name = 'Albania' )
The second query is an aggregation-on-the-fly of these national
statistics to its regions. The result is for example not "Germany,
France, Algeria, ...", but "Europe, Africa, ..."
SELECT
COALESCE(r.name, ''''),
year_start AS year,
SUM(value) AS value
FROM
life_expect AS d
LEFT JOIN
countries_view AS c ON c.id = id_country
RIGHT JOIN
regions AS r ON r.id = c.reg_id
WHERE
(year_start = 1995 OR year_start = 2000 ) AND
(r.name = 'Europe')
GROUP BY
r.name, year_start
Now, I want to enable queries which display national as well as
regional values. I could probably work with independent queries, but
I think it would be "cleaner" and more efficient to get everything
into a single query.
Can someone give me a hint how this would work?
Thanks a lot!
Stef
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2007-10-24 13:25:16 | Re: "Concatenate" two queries - how? |
Previous Message | Alvaro Herrera | 2007-10-24 13:17:19 | Re: initdb: file "/usr/local/share/postgresql/snowball_create.sql" does not exist |