From: Lee Harr <missive@frontiernet.net>
Subject: Re: Diffcult query
Date: Sat, 22 Mar 2003 03:59:18 +0000 (UTC)
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 36
Message-ID: <b5gn2l$1s2o$2@news.hub.org>
References: <d9Iea.117330$JE5.48428@news2.central.cox.net>
Reply-To: missive@hotmail.com
User-Agent: slrn/0.9.7.3 (FreeBSD)
To: pgsql-sql@postgresql.org

In article <d9Iea.117330$JE5.48428@news2.central.cox.net>, Dan Winslow wrote:
> 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.
> 
> 


Maybe you could create a function that will return all of the rows
with a particular pair of codes.

Then I think you could GROUP BY that function...