title: MySql: How to get SUM values from different tables?
body: <p>For each order I want get total for products and total of payments to get a balance. </p>
  
  <p>This is a reduced version of tables:</p>
  
  <pre><code>ORDERS
  --------------------
  |ord_id|customer_id|
  --------------------
  |     1|        XYZ|
  |     .|          .|
  |     .|          .|
  |     .|          .|
  --------------------
  
  ORDER_DETAILS
  -----------------------------------------
  |det_id|ord_id|product_id|quantity|price|
  -----------------------------------------
  |     1|     1|    AAA001|       3|   30|
  |     2|     1|    BBB002|       2|    5|
  |     .|     .|         .|       .|    .|
  |     .|     .|         .|       .|    .|
  |     .|     .|         .|       .|    .|
  -----------------------------------------
  
  PAYMENTS
  ----------------------
  |pay_id|ord_id|amount|
  ----------------------
  |     1|     1|    10|
  |     2|     1|    20|
  |     3|     1|    10|
  |     .|     .|     .|
  |     .|     .|     .|
  |     .|     .|     .|
  ----------------------
  </code></pre>
  
  <p>This query does NOT return the correct values for the payments, only get a correct value for payments when count of products is the same for count of payments:</p>
  
  <pre><code>SELECT o.ord_id, SUM(quantity * price) AS total_order, SUM(amount) AS total_payments
  FROM orders AS o 
  INNER JOIN order_details AS d ON o.ord_id = d.ord_id
  INNER JOIN payments AS p ON o.ord_id = p.ord_id
  GROUP BY o.ord_id
  </code></pre>
  
  <p>This is the expected result:</p>
  
  <pre><code>-----------------------------------
  |ord_id|total_order|total_payments|
  -----------------------------------
  |     1|        100|            40|
  |     .|          .|             .|
  |     .|          .|             .|
  |     .|          .|             .|
  -----------------------------------
  </code></pre>
  
  <p>Thanks in advance.</p>