On Nov 19, 2007 1:34 AM, Stefan Schwarzer <stefan.schwarzer@xxxxxxxxxxxx> 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'' ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match