title: update table with the sum of column value by each id in postgresql
body: <p>I have table A like this:</p>
  
  <pre><code>id     amount
  1      2
  1      3
  2      0
  1      4
  2      1
  </code></pre>
  
  <p>How could I update it like this :
  (calculate the sum of amount by id and delete duplicate rows)</p>
  
  <pre><code>id  amount
  1   9
  2   1
  </code></pre>
  
  <p>I tried:</p>
  
  <pre><code>update a
  set amount=sum(amount)
  from a group by id;
  </code></pre>
  
  <p>and got syntax error, still working on it...</p>
  
  <p>The actual table I use is:</p>
  
  <pre><code>row_id | subject_id | hostay
  ----------------------------
  </code></pre>
  
  <p>I need to update hostay with sum(hostay) and delete duplicated rows by its subject_id. </p>
  
  <p>The command I tried:</p>
  
  <pre><code>WITH cte AS (DELETE FROM ht returning *)
  INSERT INTO ht (row_id,subject_id,hostay)
  SELECT row_id,subject_id,SUM(hostay)
  FROM cte 
  GROUP BY row_id,subject_id;
  </code></pre>
  
  <p>and the duplicated rows still exist.</p>