SEO

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


The global total can be derived by for example be letting out the
COALESCE and GROUP BY r.name parameters in the SQL below and using
UNION ALL to attach this then as second query. But then, there is one
column less - the one for the regional name.

SELECT * FROM crosstab( '
SELECT
COALESCE(r.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 = 2001 OR year_start = 2002 OR year_start = 2003
GROUP BY
r.name,
year_start
ORDER BY
1,2;
', 3) AS ct(name varchar, y_2001 numeric, y_2002 numeric, y_2003
numeric)
ORDER BY
name ASC

Is there a way to substitute this with a "static" value, such as
"Global"? So, that the query still results in three columns?

Thanks for any advice!

Stef

__________________________________________________ __________________

Stefan Schwarzer

Lean Back and Relax - Enjoy some Nature Photography:
http://photoblog.la-famille-schwarzer.de

Appetite for Global Data? UNEP GEO Data Portal:
http://geodata.grid.unep.ch
__________________________________________________ __________________

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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

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