title: SQL: Return different SUM depending on CASE
body: <p>I have two tables with a <code>Header/Detail</code> relationship and I am creating a view.<br />
  The calculation of one of the columns on the view (<code>OrderQty</code>) depends on the value of one of the columns on the heard table (<code>Status</code>).<br />
  So depending on the <code>Status</code> I want the <code>OrderQty</code> to be the sum of different field, this is what I have tried:</p>
  <pre class="lang-sql prettyprint-override"><code>       (CASE
               WHEN so.Status = 10 THEN (SELECT SUM(sod.OrderQty - sod.BackOrderQty - sod.CancelQty) AS v
                                         FROM dbo.SalesOrderDetail sod
                                         WHERE sod.SalesOrderID = so.SalesOrderID
                                           AND sod.Status &lt;&gt; 99)
               WHEN so.Status = 20 THEN (SELECT SUM(sod.DespatchQty)
                                         FROM dbo.SalesOrderDetail sod
                                         WHERE sod.SalesOrderID = so.SalesOrderID
                                           AND sod.Status &lt;&gt; 99)
               WHEN so.Status = 30
                 OR so.Status = 40 THEN (SELECT SUM(ISNULL(sod.PODQty, sod.DespatchQty))
                                         FROM dbo.SalesOrderDetail sod
                                         WHERE sod.SalesOrderID = so.SalesOrderID
                                           AND sod.Status &lt;&gt; 99)
               WHEN so.Status = 50
                 OR so.Status = 60 THEN (SELECT SUM(sod.InvoiceQty)
                                         FROM dbo.SalesOrderDetail sod
                                         WHERE sod.SalesOrderID = so.SalesOrderID
                                           AND sod.Status &lt;&gt; 99)
          END) AS OrderQty;
  </code></pre>
  <p>The whole view is:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT so.SalesOrderID,
         so.SalesOrderJobTypeID,
         so.CarrierCodeID,
         so.SalesOrderTransportStatusID,
         so.CollectionDateTime,
         so.CustomerReference,
         so.FileReference,
         so.RequiredDate,
         so.SpecialInstructions,
         so.DeliveryNotes,
         dbo.ufn_SO_DecodeSOHeaderStatus(so.Status) AS Status,
         so.TransportCost,
         so.TransportRevenue,
         so.CurrencyCode,
         customer.AccountCode,
         customer.CustomerName,
         customer.Address1,
         customer.Address2,
         customer.Address3,
         customer.Address4,
         customer.PostCode,
         (SELECT UserName
          FROM dbo.SecurityUser
          WHERE dbo.SecurityUser.UserID = so.CreatedByUserID) AS CreatedByUser,
         (SELECT WeekNumber
          FROM dbo.Calendars
          WHERE dbo.Calendars.Date = so.RequiredDate) AS WeekNumber,
         (MONTH(so.RequiredDate)) AS MonthNumber,
         (SELECT ShortCode
          FROM dbo.StoreCodes
          WHERE dbo.StoreCodes.StoreCodeID = so.StoreCodeID) AS StoreCode,
         ISNULL((SELECT PickedQty
                 FROM dbo.SO_SOAllocatedContainersView
                 WHERE dbo.SO_SOAllocatedContainersView.SalesOrderID = so.SalesOrderID),
                0) AS PickedQty,
         (CASE
               WHEN so.Status = 10 THEN (SELECT SUM(sod.OrderQty - sod.BackOrderQty - sod.CancelQty) AS v
                                         FROM dbo.SalesOrderDetail sod
                                         WHERE sod.SalesOrderID = so.SalesOrderID
                                           AND sod.Status &lt;&gt; 99)
               WHEN so.Status = 20 THEN (SELECT SUM(sod.DespatchQty)
                                         FROM dbo.SalesOrderDetail sod
                                         WHERE sod.SalesOrderID = so.SalesOrderID
                                           AND sod.Status &lt;&gt; 99)
               WHEN so.Status = 30
                 OR so.Status = 40 THEN (SELECT SUM(ISNULL(sod.PODQty, sod.DespatchQty))
                                         FROM dbo.SalesOrderDetail sod
                                         WHERE sod.SalesOrderID = so.SalesOrderID
                                           AND sod.Status &lt;&gt; 99)
               WHEN so.Status = 50
                 OR so.Status = 60 THEN (SELECT SUM(sod.InvoiceQty)
                                         FROM dbo.SalesOrderDetail sod
                                         WHERE sod.SalesOrderID = so.SalesOrderID
                                           AND sod.Status &lt;&gt; 99)
          END) AS OrderQty
  FROM dbo.SalesOrderHeader so
       JOIN dbo.Customer customer ON so.CustomerID = customer.CustomerID;
  </code></pre>
  <p>This gives the error: <code>Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.</code><br />
  Does anyone understand what I am trying to do and could provide a solution?</p>