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

On Nov 19, 2007 1:34 AM, Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> wrote:
> >>
> >> 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,

Remember, you're calling this as an arg to a function, so you need to
double up your quotes...

''Global''