Thread: Data Comparison Single Table Question

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

Any pointers would be appreciated.

Bryan

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