"Concatenate" two queries - how?

Поиск
Список
Период
Сортировка
От Stefan Schwarzer
Тема "Concatenate" two queries - how?
Дата
Msg-id FE989959-DEF1-428B-A879-AFF5F57D9737@grid.unep.ch
обсуждение исходный текст
Ответы Re: "Concatenate" two queries - how?  ("Gauthier, Dave" <dave.gauthier@intel.com>)
Re: "Concatenate" two queries - how?  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Re: "Concatenate" two queries - how?  ("Stanislav Raskin" <sr@brainswell.de>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: Selecting K random rows - efficiently!
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: initdb: file "/usr/local/share/postgresql/snowball_create.sql" does not exist