Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to summarize Bridge results for each pair (north/south pair and east/west pair).

Match points for pair north/south is MPNS, for pair east/west is MPEW. Is there a way to summarize all match points for pair from MPNS and MPEW given the following data?

Boards results for sample are:
board ns ew score MPNS MPEW
1     1  2  110     0   4
1     3  4  120     2   2
1     5  6  150     4   0
2     1  4  150     4   0
2     2  6  120     2   2
2     3  5  100     0   4
3     1  6  100     1   3
3     4  2  150     4   0
3     5  3  100     1   3

and how get result for pairs:

pair sum
  1     5
  2     6
  3     5
  4     6
  5     9
  6     5
share|improve this question
    
Hi Otm, I've tried to clean up the question, but am still confused on your results. What does 'pair 1' represent, and from your provided results, how is the sum '5' for pair 1? –  Derek Downey Oct 29 '12 at 12:58
    
sum is sum(MPNS)+sum(MPEW). Pair 1 in this sample all time play on NS and 5 is 0+4+1 Sample of result bridge tournament bridgekosice.sk/upload/vysledky/n261012.htm –  otm Oct 29 '12 at 13:14

1 Answer 1

up vote 3 down vote accepted

Based on the comments, this is one possible solution:

select a.NS, Sum(a.MPNS) from
(select ns, MPNS from match_points
union all
select ew, MPEW from match_points) a
group by a.ns

which returns the required results.

SQLFiddle version of the data here - http://www.sqlfiddle.com/#!2/7e473/11

share|improve this answer
    
Thanks for answer, this work good –  otm Oct 29 '12 at 16:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.