dubstraction in a sql query

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  December 5, 2003 at 8:05 am

  #484828

  SELECT s.id, s.item, 
  
  SUM(ISNULL(e.in,0)) as 'total_in', 
  
  SUM(ISNULL(e.out,0)) as 'total_out', 
  
  SUM(ISNULL(e.in,0)-ISNULL(e.out,0)) as 'total_stock' 
  
  FROM stock s 
  
  LEFT OUTER JOIN event e 
  
  ON s.id = e.item_id 
  
  GROUP BY s.id, s.item 
  
  ORDER BY s.id, s.item 
  
   

  Edited by - davidburrows on 12/05/2003 08:05:24 AM

  Far away is close at hand in the images of elsewhere.
  Anon.

• noeld

  SSC Guru

  Points: 96590

  More actions

  December 5, 2003 at 8:13 am

  #484836

  this is my subjestion

  
  
  
  
   SELECT 
  
  stock.id, 
  
  stock.item,    
  
  COALESCE( SUM(in),0)   AS total_in, 
  
  COALESCE( SUM(out),0) AS total_out,
  
  COALESCE( SUM(in),0)   -  COALESCE( SUM(out),0) As total_stock
  
  FROM      
  
  stock 
  
  LEFT OUTER JOIN                      
  
  event 
  
  ON stock.id = event.item_id
  
  GROUP BY 
  
  stock.id, stock.item
  
  
  
   

  HTH

  
  * Noel