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