Обсуждение: 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;