samedi 27 juin 2015

How to divide between rows in one table in Postgres

I am working in Posgres with a table like this:

mon   yyyy   weather

Apr   2014   78.45

Apr   2015   77.32

May   2014   79.56

May   2015   78.43

I would like to be able to query some results, ordered by "mon", where the Weather column values are divided according to year-on-year by month.

In other words I want to query weather where Apr 2015 is divided by Apr 2014.

However, I would like to write the query in such a way that I do not have to specify Month or year, and the query automatically divides Weather values according to: Apr 2015/Apr 2014, then May 2014/May 2014 without having to key in every month and every year, which is laborious.

I have the following code, but this expands columns which is not what I want:

Blockquote

select (select "Weather" from yoy 
        where mon = 'Apr' and yyyy = '2015'
     )/(select "American" from yoy 
        where mon = 'Apr' and yyyy = '2014'
     ) as "weather_apr",
     (select "Weather" from yoy 
      where mon = 'May' and yyyy = '2015'
     )/(select "Weather" from yoy 
        where mon = 'May' and yyyy = '2014'
     ) as "weather_may",
from yoy

Blockquote

Any help with writing such a query would be much appreciated, thanks!

Aucun commentaire:

Enregistrer un commentaire