Re: Diffcult query

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