title: How to Sum multiple Column with multiple value
body: <p>I am looking for a solution to the following:</p>
  
  <ol>
  <li><p>Go in to the users table and find a user who has listed items on the site. In this users table, there is no column about auctions. Instead, it is connected to an accounts table with a key (in accounts, this column is called user)</p></li>
  <li><p>From these IDs (users which have listed items for auction), I need to find their account balance. This is also in the accounts table. The balance is contained in a column called operation_amount. I also have another column called operation_type which describes whether a user has a positive or negative balance. For example, if operation_type = 1, he has a negative balance, while if operation_type = 2, he has a positive balance.</p></li>
  </ol>
  
  <p>Now I have another table called <code>tmpinvoice</code> where there is a column called amount. This shows how much in fees a user needs to pay to the site administrators.</p>
  
  <p>Given this, I need to calculate how much he must pay in total. For example, if a user has a $200 balance, I need to check whether it's negative or positive based on the <code>operation_type</code>.</p>
  
  <p>So I have query where is Do this only for record </p>
  
  <pre><code>SELECT u.id AS id_user, u.nick,
    CASE ac.operation_type WHEN 1 THEN ac.operation_amount - tm.amount
                           WHEN 2 THEN ac.operation_amount + tm.amount
                                  ELSE 'N/A' END AS `fee`                         
  FROM auctionbg_search.accounts AS ac    
      LEFT JOIN auctionbg_search.users AS u ON TRUE
          AND u.id = ac.user
      LEFT JOIN auctionbg_search.auctions AS a ON TRUE
          AND a.id = ac.auction
      LEFT JOIN auctionbg_search.tmpinvoice AS tm  ON TRUE    
  WHERE TRUE
    AND tm.amount = ac.operation_amount
  </code></pre>
  
  <p>Here what result I'm receiving </p>
  
  <p><a href="http://gyazo.com/3d7e7f52ee14d21cc8c8d33b6bbc479a" rel="nofollow">http://gyazo.com/3d7e7f52ee14d21cc8c8d33b6bbc479a</a></p>
  
  <p>Yes but this calculate 'fee' only for 1 value in column , what if user have multiple values </p>
  
  <p>like this user :</p>
  
  <p><a href="http://gyazo.com/c3bdb29fa235044ab888dc0385bbcdbd" rel="nofollow">http://gyazo.com/c3bdb29fa235044ab888dc0385bbcdbd</a></p>
  
  <p>I need calculate total amount from operation_amount  of that given user and remove <code>tmpinvoice</code> from that total amount  , </p>
  
  <p>A friend of mine told me to use </p>
  
  <pre><code>IF(SUM(ac.operation_amount), IS NULL , 0, sum(ac.operation_amount) 
  </code></pre>
  
  <p>and to join 2 time accounts(table) for both case with + and -</p>
  
  <p>join 1 time for + , 2 time for -</p>
  
  <p>but I can't figure out how will looks :)</p>