Calculation of per Capita on-the-fly - problems with SQL syntax - Mailing list pgsql-general

From Stefan Schwarzer
Subject Calculation of per Capita on-the-fly - problems with SQL syntax
Date
Msg-id BAD0F6D2-57E7-4A5B-AD2B-1EB55CFBD441@grid.unep.ch
Whole thread Raw
Responses Re: Calculation of per Capita on-the-fly - problems with SQL syntax  (Nis Jørgensen <nis@superlativ.dk>)
List pgsql-general
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




pgsql-general by date:

Previous
From: Karsten Hilbert
Date:
Subject: XMIN semantic at peril ?
Next
From: Tom Lane
Date:
Subject: Re: XMIN semantic at peril ?