title: Sum case in SQL Server
body: <p>The below query get total sales for certain outlet.</p>
  
  <p>Output:</p>
  
  <pre><code>SourceName       Amount
  ------------------------
  Call Center      2997.00
  Delivero Credit  5879.00
  Eat Easily Cash    57.00
  </code></pre>
  
  <p>If I have a list of outlets, outlet 1 and outlet 2 for example, I want so to get total sales by source for each outlet and if there no sales sales amount value should be zero so out put will be.</p>
  
  <pre><code>SourceName      outlet 1   outlet 2
  -----------------------------------
  Call Center      2997.00    500
  Delivero Credit  5879.00      0
  Eat Easily Cash    57.00     66
  </code></pre>
  
  <p>The issue on the below query it double sum <code>DeliveryCharge</code>
  it should be  <code>SUM((OrderDetails.Quantity * OrderDetails.UnitPrice)) + Orders.DeliveryCharge - ((SUM((OrderDetails.Quantity * OrderDetails.UnitPrice))) * Orders.Discount / 100)</code></p>
  
  <p>Updated query:</p>
  
  <pre><code>SELECT
      s.SourceName,
      SUM(CASE
             WHEN o.OutletID ='1' THEN ((ord.Quantity * ord.UnitPrice) + ordd.DeliveryCharge)
             ELSE 0
          END) AS [Outlet 1],
      SUM(CASE
             WHEN o.OutletID ='1' THEN ((ord.Quantity * ord.UnitPrice) + ordd.DeliveryCharge)
             ELSE 0
          END) AS [Outlet 2]
  FROM 
      dbo.Orders ordd
  INNER JOIN 
      dbo.Outlets o ON ordd.OutletID = o.OutletID
  INNER JOIN 
      dbo.OrderDetails ord ON ord.OrderID = ordd.OrderID
  LEFT OUTER JOIN 
      dbo.Sources s ON s.SourceID = ordd.SourceID
  WHERE  
      ordd.OrderID NOT IN (SELECT CanceledOrders.OrderID
                           FROM CanceledOrders)
      AND ordd.OrderDate BETWEEN '2017-05-1 10:00:00.000' AND '2017-06-1 04:00:00.000'
  GROUP BY 
      s.SourceID, s.SourceName
  ORDER BY
      s.SourceName
  </code></pre>