Substitute column in SELECT with static value?

On Nov 16, 2007 4:26 AM, Stefan Schwarzer <stefan.schwarzer@grid.unep.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.

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

On Nov 19, 2007 1:34 AM, Stefan Schwarzer <stefan.schwarzer@grid.unep.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''