> > Hi folks, seeking help with a query that I thought was simple, but > apparantly isn't, at least for someone with my knowledge level. > > Given a table : > > create table atable ( > code1 char, > code2 char, > cost int > ); > > And the rows > > code1 code2 cost > ----------------------------- > a b 2 > d e 4 > b a 6 > f g 1 > > I need a ( preferably single ) query that will sum the costs for any > matching pairs of codes regardless of order. That is, row 1 and row 3 > concern the same pair of unordered codes (a,b), and the result should show > that the (a,b) pair had a summed cost of 8. I am not able to change any of > the environment or preconditions other than the query itself. I have tried > so many approaches that they aren't even worth listing. Any suggestions > would be very much appreciated. > My approach is inspired by PostgreSQL 7.3 Set Returning Functions by Stephan Szabo ( http://techdocs.postgresql.org/guides/SetReturningFunctions )
Use a set returning function to get ordered pairs and do a group by then as create type aholder as ( code1 char, code2 char, cost int ); create or replace function aordered() returns setof aholder as ' declare myrow aholder%ROWTYPE; codex char; begin for myrow in select code1,code2,cost from atable loop if myrow.code1 > myrow.code2 then codex := myrow.code1; myrow.code1 := myrow.code2; myrow.code2 := codex; end if; return next myrow; end loop; return; end ' language 'plpgsql'; select code1,code2,sum(cost) from aordered() group by code1,code2 ; code1 | code2 | sum -------+-------+----- a | b | 8 d | e | 4 f | g | 1 (3 rows) In addition if you want only matching pairs (eliminate single pairs) try create or replace function amatched() returns setof aholder as ' declare myrow aholder%ROWTYPE; codex char; begin for myrow in select t1.code1,t1.code2,t1.cost from atable t1,atable t2 where t1.code1=t2.code2 and t1.code2=t2.code1 loop if myrow.code1 > myrow.code2 then codex := myrow.code1; myrow.code1 := myrow.code2; myrow.code2 := codex; end if; return next myrow; end loop; return; end ' language 'plpgsql'; select code1,code2,sum(cost) from amatched() group by code1,code2 ; code1 | code2 | sum -------+-------+----- a | b | 8 (1 row) Hope this helps. Regards, Christoph ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]