Search Postgresql Archives

Substitute column in SELECT with static value?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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: 
  
  Appetite for Global Data? UNEP GEO Data Portal:  
  ____________________________________________________________________






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux