title: how to use group by with inner join
body: <p>I have three tables <strong>customer</strong> , <strong>bargains</strong> and <strong>installment</strong>.<br>
  I want to select <em>name</em> from <strong>customer</strong> table and sum of <em>total price</em> from <strong>bargains</strong> table and sum of <em>payment amount</em> from <strong>installment</strong> table according to each customer and at the end subtract the sum of total price from sum of payment amount.<br>
  so the result should be a list that contain name of all customers and their debt.  </p>
  
  <p>I try the following command but it return wrong values it looks like they are summed twice or maybe more.  </p>
  
  <pre><code>select c.name, b.total_price, i.payment_amount
  from customer as c
  inner join (select sell_or_buy, sum(total_price) as total_price from     bargains group by C_ID ) as b on (b.sell_or_buy = 'خرید')
  inner join (select trade_type, sum(payment_amount) as payment_amount from installment group by C_ID ) as i on (i.trade_type = 'پرداخت')
  group by c.C_ID
  </code></pre>
  
  <p>because I thought query sum the total price for each record of installment table and sum the payment amount for each record of bargains table I try to use select in inner joins to make each of them distinct but it doesn't work
  so I couldn't even subtract the results.<br>
  I also try the following query  </p>
  
  <pre><code>select
  (select sum(payment_amount) as payment_amount from installment  where trade_type = 'پرداخت'group by C_ID)-
  (select sum(total_price) as total_price from bargains where sell_or_buy = 'خرید' group by C_ID)as result
  </code></pre>
  
  <p>which display and error which say query return more than one row.<br>
  when I use only one select from above query the result is OK but when I try to combine the its not working.</p>