SEO

#1 (**permalink**) 04-19-2008, 06:02 PM

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#2 (**permalink**) 04-19-2008, 06:02 PM

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Data Comparison Single Table Question