Diffcult query

From: "Dan Winslow" <d(dot)winslow(at)cox(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Diffcult query
Date: 2003-03-21 17:41:29
Message-ID: d9Iea.117330$JE5.48428@news2.central.cox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi folks. This is giving me fits. I feel like it oughta be simple, but
apparantly its not...I think.

Given a table :

create table tablea (
code1 varchar(32),
code2 varchar(32),
cost int
);

and the rows

code1 code2 cost
----------------------------------
"aaa" "bbb" 2
"ddd" "eee" 3
"bbb" "aaa" 6
"ggg" "hhh" 4

I need a ( preferably single ) query that will sum the costs for all unique
pairs of codes, without regard to column order. That is, for summing
purposes, rows 1 and 3 are identical, and should produce the sum of 8 for
the unordered pair("aaa","bbb"). It should also, of course, prevent the case
where ("bbb","aaa") is considered a seperate pair. Any ideas would be much
appreciated.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Achilleus Mantzios 2003-03-21 17:54:56 Re: cast of integer to bool doesn't work (anymore?)
Previous Message mallah 2003-03-21 16:32:11 Re: explain