Q:

Grabbing user score in PL/SQL

So my dilemma is trying to figure out the total score of a user in the game of chess. That particular user could be either play as a white user or a black user. Here's the code:
create table user(
u_id int,
u_name varchar(50),
u_grade int,
u_rating int,
primary key (u_id));

create table competition(
c_id int,
c_name varchar(255),
c_date date,
primary key (c_id));

create table matchup(
b_id int,
c_id int, 
turn int, 
x_pid int, 
y_pid int, 
x_score number, 
y_score number, 
primary key(b_id,c_id,turn),
foreign key(c_id) references competition,
foreign key(x_pid) references user,
foreign key(y_pid) references user);

insert into matchup values(1,1,1,1,2,0,1);
insert into matchup values(2,1,1,3,4,0.5,0.5);
insert into matchup values(11,1,1,5,6,1,0);
insert into matchup values(12,1,1,7,8,0,1);
insert into matchup values(1,1,2,2,3,0,1);
insert into matchup values(2,1,2,4,1,1,0);
insert into matchup values(11,1,2,8,5,1,0);
insert into matchup values(12,1,2,6,7,0.5,0.5);

Lets say, for the sake of our purposes, 1 is the u_id of Bob. How would i figure out the total score of bob based upon the score he got when he played as x_pid + when he played as y_pid? Here's what i had so far,
CURSOR c1 IS SELECT (SUM(x_score) + SUM(y_score)) AS total_score FROM matchup JOIN user u1 ON matchup.y_pid = u1.u_id
JOIN user u2 ON matchup.x_pid = u2.u_id JOIN competition ON matchup.c_id = competition.c_id WHERE
(u1.u_name = 'Bob' OR u2.u_name = 'Bob') AND  commpetition.c_name = 'Wonderland';

This query seems to be giving the same output for all the users i tried, the number 2, as the total. What am i doing wrong? Because for Bob, the total score should be 0 as he didn't win any game (0 score is a no win, 0.5 is a score of draw, and 1 is a score of win)

A:

Your query in simplified form:
select (sum(x_score) + sum(y_score)) as total_score
  from matchup
 where x_pid = 1
    or y_pid = 1;

It calculates sum of Bob's score and score of his opponent. So you need to separate one from another.
Example 1:
select (sum(decode(x_pid, 1, x_score, 0)) + sum(decode(y_pid, 1, y_score, 0))) as total_score
  from matchup
 where x_pid = 1
    or y_pid = 1;

Example 2:
select sum(s) as total_score
  from (select sum(x_score) s
          from matchup
         where x_pid = 1
        union all
        select sum(y_score) 
          from matchup
         where y_pid = 1);

