Hi there,
I run an aggregation on national statistics to retrieve regional
values (for
Africa, Europe, ...). Now, I want to have a global aggregation as
well. The
easiest thing for my PHP/HTML procedure would be to have the
global row make
appear within the regional result. So it would be something like
name | y_2001 | y_2002 .....
--------------------------------------------------------
Africa | 2323 | 342323
Europe | ....
.....
Global | 849309 | .....
Is there a way to substitute this with a "static" value, such as
"Global"?
So, that the query still results in three columns?
Sure, just include it as 'Global'
Note the single, not double, quotes.
That's what I thought at the beginning too. But it didn't work.
Both queries are being executed separately correctly.
SELECT * FROM crosstab( '
SELECT
COALESCE(r.name, '''') AS name,
year_start AS year,
SUM(value) AS value
FROM
co2_total_cdiac AS d
RIGHT JOIN
countries_view AS c ON c.id = id_country
RIGHT JOIN
regions AS r ON r.id = c.reg_id
WHERE
year_start = 2002
GROUP BY
r.name,
year_start
UNION ALL
SELECT
'Global' AS name,
year_start AS year,
SUM(value) AS value
FROM
co2_total_cdiac AS d
RIGHT JOIN
countries_view AS c ON c.id = id_country
RIGHT JOIN
regions AS r ON r.id = c.reg_id
WHERE
year_start = 2002
GROUP BY
year_start
ORDER BY
1,2;
', 3) AS ct(name varchar, y_2001 numeric, y_2002 numeric, y_2003
numeric)
ORDER BY
name ASC
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
