From: | Osvaldo Rosario Kussama <osvaldo(dot)kussama(at)gmail(dot)com> |
---|---|
To: | Bryan Emrys <bryan(dot)emrys(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Data Comparison Single Table Question |
Date: | 2008-04-16 15:09:01 |
Message-ID: | 4806168D.6080802@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Bryan Emrys escreveu:
> I can handle this outside sql, but it seems like I should be able to do this in sql as well.
>
> 1 table: countries.
> 3 columns: id, name, price
>
> What I'm trying to get is a result of the price differences between every country.
>
> So if the data looks like (ignoring the id field)
>
> Taiwain 30
> UK 50
> US 40
>
> I'm trying to build a matrix that looks like:
> Taiwan UK US
> Taiwan 0 -20 -10
> UK 20 0 10
> US 10 -10 0
>
Not in a matrix form:
bdteste=# SELECT f1.name,f1.price,f2.name,f1.price-f2.price AS
difference FROM foo f1 CROSS JOIN foo f2;
name | price | name | difference
---------+-------+---------+------------
Taiwain | 30 | Taiwain | 0
Taiwain | 30 | UK | -20
Taiwain | 30 | US | -10
UK | 50 | Taiwain | 20
UK | 50 | UK | 0
UK | 50 | US | 10
US | 40 | Taiwain | 10
US | 40 | UK | -10
US | 40 | US | 0
(9 registros)
Osvaldo
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2008-04-16 15:49:23 | Re: How to find double entries |
Previous Message | Vivek Khera | 2008-04-16 14:27:49 | Re: How to find double entries |