Re: Diffcult query - Mailing list pgsql-sql

From Lee Harr
Subject Re: Diffcult query
Date
Msg-id b5gn2l$1s2o$2@news.hub.org
Whole thread Raw
In response to Diffcult query  ("Dan Winslow" <d.winslow@cox.net>)
List pgsql-sql
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...



pgsql-sql by date:

Previous
From: Josh Berkus
Date:
Subject: Re: Diffcult query
Next
From: Popeanga Marian
Date:
Subject: Re: explain