title: Adding sub queried column to complex select query
body: <p>I have the following query:</p>
  
  <pre><code>(SELECT phppos_sales.sale_id as sale_id, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)+(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as total 
  
  FROM phppos_sales_items
  INNER JOIN phppos_sales ON phppos_sales_items.sale_id=phppos_sales.sale_id 
  INNER JOIN phppos_items ON phppos_sales_items.item_id=phppos_items.item_id 
  LEFT OUTER JOIN phppos_sales_items_taxes ON phppos_sales_items.sale_id=phppos_sales_items_taxes.sale_id and phppos_sales_items.item_id=phppos_sales_items_taxes.item_id and phppos_sales_items.line=phppos_sales_items_taxes.line 
  
  WHERE sale_time BETWEEN "2014-08-01 00:00:00" and "2014-08-09 23:59:59" 
  
  GROUP BY phppos_sales.sale_id, phppos_sales_items.item_id, phppos_sales_items.line) 
  </code></pre>
  
  <p>Example Result:</p>
  
  <pre><code>| sale_id | total                              |
  +---------+------------------------------------+
  |    3863 | 172.778400000000000000000000000000 |
  |    3863 | 362.815200000000000000000000000000 |
  |    3863 | 129.578400000000000000000000000000 |
  |    3863 | 215.973000000000000000000000000000 |
  |    3863 | 615.519000000000000000000000000000 |
  +---------+------------------------------------+
  </code></pre>
  
  <p>I want to add a third column payment_amount for each sale. To find the payment amount the following query is executed.</p>
  
  <pre><code>SELECT SUM(payment_amount) from phppos_sales_payments WHERE sale_id = XXX;
  </code></pre>
  
  <p>Is there a way to do this in 1 query?</p>