Thread: Diffcult query

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.

Dan,

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

> 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.

It's not simple, because your DB design is poorly normalized.   My first
suggestion would be to make some design changes to your schema.  Is that
possible?

--
-Josh BerkusAglio Database SolutionsSan Francisco

Dan,

> No, it is not my design, unfortunately.

Then there is no simple way.

> > 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.

1) Create a function which will take two text variables and concatenate them
in alpha sort order.    The idea of this function:
fn_distinct_pair('aaa','bbb') = 'aaa bbb'
fn_distinct_pair('bbb','aaa') = 'aaa bbb'
Thus:
CREATE FUNCTION fn_distinct_pair (text, text) returns text as'
SELECT (CASE WHEN $1 < $2 THEN                        $1 || '' '' || $2               ELSE                       $2 ||
'''' || $1               END); 
' LANGUAGE SQL;
(of course, this gets much more complicated if you have more than two columns
or if any of the columns can be NULL)

2) Group by the results of that function.

Good luck!

-Josh Berkus

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...

In article <d9Iea.117330$JE5.48428@news2.central.cox.net>,
"Dan Winslow" <d.winslow@cox.net> writes:

> 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.

How about this one?
 SELECT CASE WHEN code1 < code2 THEN code1 ELSE code2 END ||        '\0' ||        CASE WHEN code1 < code2 THEN code2
ELSEcode1 END AS codes,        SUM(cost) AS costs FROM tablea GROUP BY codes;