Re: Substitute column in SELECT with static value? (Crosstab problem?)

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