From: Osvaldo Rosario Kussama <osvaldo.kussama@gmail.com>
To: Bryan Emrys <bryan.emrys@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Data Comparison Single Table Question
Date: Wed, 16 Apr 2008 12:09:01 -0300
Message-ID: <4806168D.6080802@gmail.com> (raw)
In-Reply-To: <200804160634.27304.bryan.emrys@gmail.com>
References: <200804160634.27304.bryan.emrys@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: osvaldo.kussama@gmail.com, bryan.emrys@gmail.com
Subject: Re: Data Comparison Single Table Question
In-Reply-To: <4806168D.6080802@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox