title: INNER JOIN change SUM result
body: <pre><code>CREATE TABLE beauty.customer_payments
  (
  customer_id integer,
  date date,
  amount numeric(10,2),
  CONSTRAINT customer_payments_customer_id_fkey FOREIGN KEY    (customer_id)
      REFERENCES beauty.customers (customer_id) MATCH SIMPLE
      ON UPDATE NO ACTION
      ON DELETE NO ACTION
  )
  </code></pre>
  
  <pre><code>CREATE TABLE beauty.sales
  (
  product_id integer,
  customer_id integer,
  sell_date date NOT NULL,
  qty integer NOT NULL,
  sell_price numeric(10,2) NOT NULL,
  expiry_date date NOT NULL,
  CONSTRAINT sales_customer_id_fkey FOREIGN KEY (customer_id)
      REFERENCES beauty.customers (customer_id) MATCH SIMPLE
      ON UPDATE NO ACTION
      ON DELETE NO ACTION,
  CONSTRAINT sales_product_id_fkey FOREIGN KEY (product_id)
      REFERENCES beauty.products (product_id) MATCH SIMPLE
      ON UPDATE NO ACTION
      ON DELETE NO ACTION
  )
  </code></pre>
  
  <p>Balance of payments <code>beauty.customer_payments</code> for customer_id=6 is 0</p>
  
  <pre><code> SELECT * FROM beauty.customer_payments 
   WHERE customer_id=6;
  </code></pre>
  
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>customer_id</th>
  <th>date</th>
  <th>amount</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6</td>
  <td>2020-11-14</td>
  <td>75.00</td>
  </tr>
  <tr>
  <td>6</td>
  <td>2020-11-14</td>
  <td>-75.00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code> SELECT * FROM beauty.sales 
   WHERE customer_id=6;
  </code></pre>
  
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>product_id</th>
  <th>customer_id</th>
  <th>sell_date</th>
  <th>qty</th>
  <th>sell_price</th>
  <th>expiry_date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>76</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>75.00</td>
  <td>2022-03-03</td>
  </tr>
  <tr>
  <td>83</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>10.00</td>
  <td>2022-06-23</td>
  </tr>
  <tr>
  <td>85</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>10.00</td>
  <td>2022-06-23</td>
  </tr>
  <tr>
  <td>44</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>12.00</td>
  <td>2022-06-23</td>
  </tr>
  <tr>
  <td>41</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>15.00</td>
  <td>2022-03-26</td>
  </tr>
  <tr>
  <td>96</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>75.00</td>
  <td>2022-03-15</td>
  </tr>
  <tr>
  <td>28</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>4.00</td>
  <td>2022-01-22</td>
  </tr>
  <tr>
  <td>33</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>4.00</td>
  <td>2023-01-23</td>
  </tr>
  <tr>
  <td>37</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>4.00</td>
  <td>2023-01-23</td>
  </tr>
  <tr>
  <td>40</td>
  <td>6</td>
  <td>2020-11-14</td>
  <td>1</td>
  <td>4.00</td>
  <td>2023-08-13</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>(10 rows)</p>
  
  <pre><code>SELECT customer_id, SUM(qty * sell_price) AS purchased
  FROM beauty.sales
  WHERE customer_id=6
  GROUP BY customer_id;
  </code></pre>
  
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>customer_id</th>
  <th>purchased</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6</td>
  <td>213.00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>SELECT s.customer_id, 
  SUM(qty * sell_price) AS purchased, 
  SUM(cp.amount) AS paid,
  SUM(qty * sell_price - cp.amount) AS balance
  FROM beauty.sales s
  INNER JOIN beauty.customer_payments cp 
  ON cp.customer_id = s.customer_id
  WHERE s.customer_id=6 
  GROUP BY s.customer_id;
  </code></pre>
  
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>customer_id</th>
  <th>purchased</th>
  <th>paid</th>
  <th>balance</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6</td>
  <td>1065.00</td>
  <td>0.00</td>
  <td>1065.00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Please advise WHY after adding <code>JOIN (INNER, LEFT, RIGHT)</code> calculation goes wrong and how to solve this multi-calculation issue?
  As I see similar question all of them based on not cross-tables calculations like <code>SUM(qty * sell_price - cp.amount)</code></p>
  
  <p>Delete payments</p>
  `
  <pre><code>DELETE FROM beauty.customer_payments
  WHERE customer_id=6;`
  </code></pre>
  
  <p>Add new ZERO payment</p>
  
  <pre><code>INSERT INTO beauty.customer_payments(
  customer_id, date, amount)
  VALUES (6, '2020-11-17', 0);
  </code></pre>
  
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>customer_id</th>
  <th>purchased</th>
  <th>paid</th>
  <th>balance</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6</td>
  <td>213.00</td>
  <td>0.00</td>
  <td>213.00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Add payment 10</p>
  
  <pre><code>INSERT INTO beauty.customer_payments(
  customer_id, date, amount)
  VALUES (6, '2020-11-17', 10);
  
  SELECT * FROM beauty.customer_payments WHERE customer_id=6;
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>customer_id</th>
  <th>date</th>
  <th>amount</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6</td>
  <td>2020-11-17</td>
  <td>0.00</td>
  </tr>
  <tr>
  <td>6</td>
  <td>2020-11-17</td>
  <td>10.00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>SELECT s.customer_id, 
  .......
  INNER JOIN beauty.customer_payments cp 
  .......
  </code></pre>
  
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>customer_id</th>
  <th>purchased</th>
  <th>paid</th>
  <th>balance</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6</td>
  <td>426.00</td>
  <td>100.00</td>
  <td>326.00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Correct payment with negative amount</p>
  
  <pre><code>INSERT INTO beauty.customer_payments(
  customer_id, date, amount)
  VALUES (6, '2020-11-17', -10);
  </code></pre>
  
  <pre><code>SELECT * FROM beauty.customer_payments WHERE customer_id=6;
  </code></pre>
  
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>customer_id</th>
  <th>date</th>
  <th>amount</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6</td>
  <td>2020-11-17</td>
  <td>0.00</td>
  </tr>
  <tr>
  <td>6</td>
  <td>2020-11-17</td>
  <td>10.00</td>
  </tr>
  <tr>
  <td>6</td>
  <td>2020-11-17</td>
  <td>-10.00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>SELECT s.customer_id, 
  .......
  INNER JOIN beauty.customer_payments cp 
  .......
  </code></pre>
  
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>customer_id</th>
  <th>purchased</th>
  <th>paid</th>
  <th>balance</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6</td>
  <td>639.00</td>
  <td>0.00</td>
  <td>639.00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What is this `INNER JOIN' calculate?</p>