Search Postgresql Archives

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

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

 



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;


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[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