[GENERAL] Calculation of per Capita on-the-fly - problems with SQL syntax

[Stefan Schwarzer]

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,

Stef




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