Hi there,
I have a database with statistical tables which look like this:
id | year_start | value
3 1960 736.1
3 1961 723.3
....
4 1960 123.4
Years can cover 40 years for each of the countries (ID) and each (variable) table .
Now, if for a given year there is no value, expressed not in form of NoData, but as missing line in the table, than I have a problem with this SQL:
SELECT
*
FROM
crosstab( 'SELECT
COALESCE(
c.name, '''') AS name,
d.year_start AS year,
d.value AS value
FROM
co2_total_cdiac AS d
RIGHT JOIN
countries_view AS c ON c.id = d.id_country
WHERE
(d.year_start = 1960 OR d.year_start = 2007 )
ORDER BY
1,2;', 3) AS ct (name varchar, y_1960 numeric, y_2007 numeric)
For one of the countries, I have a value for 2007, but not for 1960. When using only the inner query, than I see one line: Andorra - 2007 - 539
But when running the whole SQL, the value for year 2007 get's allocated to the year 1960. The table looks as follows:
name | y_1960 | y_2007
Andorra 539 NULL
(Not sure if the copy/pasted part displays correct:
| name | y_1960 | y_2007 |
|---|
| Afghanistan | 415 | 716 |
| Albania | 2026 | 4246 |
| Algeria | 6166 | 140234 |
| Andorra | 539 | NULL |
)
Can anyone give me a hint in which direction to search for a solution?
Thanks a lot!
Stefan