title: Subtract data from field - PostgreSQL
body: <p>I've searched for an equivalent of the AGGREGATION function <code>sum()</code> in PostgreSQL but didn't find any.</p>
  
  <p>I need to subtract the data of a field, from the data of another one.</p>
  
  <p>The code goes as follows:</p>
  
  <pre><code>'''SELECT
              p.purchase_id,sum(m.Cantidad), m.state
          FROM
              stock_move m
          LEFT JOIN
              stock_picking p on (p.id=m.picking_id)
          WHERE
              p.purchase_id IN %s GROUP BY m.state, p.purchase_id'''
  </code></pre>
  
  <p>in this case <code>purchase_id</code> sums to <code>Cantidad</code> it's data, and gets stored in <code>stock_picking</code> however i need to actually subtract instead of sum, there's no subtract function in PostgreSQL as far as i know, so which function should i use to accomplish this task?</p>
  
  <p>EDIT:</p>
  
  <p>I have 2 modules, in OpenErp, which manage products, (sells, stock, purchases, etc), when i approve a purchase order, i need to subtract, the product quantity of the purchase from a field called <code>Cantidad</code> in another module, so, i know how to relate them in OpenErp, problem is i don't know how to subtract the quantity from this <code>Cantidad</code>.</p>
  
  <p>As far as i know, the function that sums the products to be received from the <code>purchase order</code> to the <code>product_qty</code> in <code>stock</code> is as follows:</p>
  
  <pre><code>def desc_cert(self, cr, uid, ids, context=None):
      if not ids: return {}
      res = {}
      for id in ids:
          res[id] = [0.0,0.0]
      cr.execute('''SELECT
              p.purchase_id,sum(m.product_qty), m.state
          FROM
              stock_move m
          LEFT JOIN
              stock_picking p on (p.id=m.picking_id)
          WHERE
              p.purchase_id IN %s GROUP BY m.state, p.purchase_id''',(tuple(ids),))
      for oid,nbr,state in cr.fetchall():
          if state=='cancel':
              continue
          if state=='done':
              res[oid][0] += nbr or 0.0
              res[oid][1] += nbr or 0.0
          else:
              res[oid][1] += nbr or 0.0
      for r in res:
          if not res[r][1]:
              res[r] = 0.0
          else:
              res[r] = 100.0 * res[r][0] / res[r][1]
      return res
  </code></pre>
  
  <p>So, i think i need a much smaller and simpler function to get this subtraction from my <code>Cantidad</code> field. In my case, i don't need any stock or picking parameter, just subtract from the <code>purchase_id</code> the products contained in it (by grouping them as this example do) and use the field from the other module called <code>Cantidad</code> instead of <code>product_qty</code>.</p>
  
  <p>I hope i've explained myself.</p>