Re: Calculation of per Capita on-the-fly - problems with SQL syntax

Stefan Schwarzer skrev:
> Hi there,
>
> I need to calculate per Capita data on-the-fly. My table for a given
> variable looks like this:
>
> year | value | id_country
> ---------------------------------------
> 2001 | 123 | 1
> 2002 | 125 | 1
> 2003 | 128 | 1
> 2004 | 132 | 1
> 2005 | 135 | 1
>
> 2001 | 412 | 2
> 2002 | 429 | 2
> 2003 | 456 | 2
> 2004 | 465 | 2
> 2005 | 477 | 2
>
>
> Now, I can't get the calc working correctly. I use the query below, but
> a) it just takes too much time to come up with a result; and b) the
> results has three lines for each country,
> one with a value for y_2003 and a NULL for y_2002
> one with a NULL for y_2003 and a value for y_2002
> one with a NULL for both y_2003 and y_2002
>
>
> SELECT DISTINCT
> ( CASE WHEN d.year=2003 AND pt.year=2003 AND pt.value <> '0' AND
> pt.value IS NOT NULL THEN d.value / pt.value ELSE NULL END ) AS y_2003,
> ( CASE WHEN d.year=2002 AND pt.year=2002 AND pt.value <> '0' AND
> pt.value IS NOT NULL THEN d.value / pt.value ELSE NULL END ) AS y_2002,
> c.name
> FROM
> public_multiple_tables.agri_area AS d
> LEFT JOIN
> public_multiple_tables.pop_total AS pt ON pt.id_country = d.id_country
> LEFT JOIN
> countries_view AS c ON c.id = d.id_country
> ORDER BY
> name ASC
>
>
> What am I doing wrong? Thanks for any advice,

You are trying to do the join on the year in the SELECT expression.
Also, you are trying to do the formatting into year-columns in your
query. You are left joining to tables in which there should always be a
mathing row (I assume).

This should give you the same data out in a different format. Note that
most of the NULL values will be excluded from this result.

SELECT cname, year, d.value/pt.value
FROM
public_multiple_tables.agri_area AS d
INNER JOIN
public_multiple_tables.pop_total AS pt ON pt.id_country =
d.id_country AND pt.year = d.year
INNER JOIN
countries_view AS c ON c.id = d.id_country
WHERE d.year in (2002,2003,2004)
AND pt.value <> 0
ORDER by c.name, year;