title: How to get SQL Server to return a default of 0, if no rows exist?
body: <p>How can I get a default value of 0 if a sum does not return any rows?</p>
  
  <p>Edit: I have edited this post to add a more thorough example (which the previous one didn't encounter)</p>
  
  <p>E.g.</p>
  
  <pre><code> DECLARE @Item TABLE
  (
      Id int,
      Price decimal,
      PricePer decimal
  )
  
  DECLARE @OrderItem TABLE
  (
      Id int,
      ItemId int,
      ChargedPrice nvarchar(10),
      QtyRequired int,
      QtyLeftToDespatch int
  )
  
  INSERT INTO @Item (Id,Price,PricePer) VALUES (1,1.00, 1)
  INSERT INTO @Item (Id,Price,PricePer) VALUES (2,2.00, 1)
  INSERT INTO @Item (Id,Price,PricePer) VALUES (3,3.00, 1)
  INSERT INTO @Item (Id,Price,PricePer) VALUES (4,4.00, 1)
  
  INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 1,1,100,100,50 )
  INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 2,1,200,300,50)
  INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 3,1,300,300,50 )
  
  DECLARE @ItemIdTest int 
  SET @ItemIdTest = 4
  
  SELECT SUM((price/priceper)*QtyRequired) as total_value,
         SUM((price/priceper)*QtyRequired) as outstanding_value
  FROM @Item i
  INNER JOIN @OrderItem o ON i.Id = o.ItemId
  WHERE i.Id = @ItemIdTest
  group by itemId
  </code></pre>
  
  <p>This will return </p>
  
  <pre><code>total_value, outstanding_value
  ==============
  &lt;No rows&gt;
  </code></pre>
  
  <p>But I want it to have 0 as the default value returned. However, if you do a SELECT to select it, then a second SELECT to select a default, 2 result sets will be returned.</p>
  
  <p>Can I do it just in one? I have looked at COALESCE, but this only works if NULL is returned, which it isn't.</p>
  
  <p><strong>Edit</strong>: I think the problem is to do with the group with, but is there a reason why still it doesn't come back with any results?</p>