How to use CASE WHEN THEN inside aggregated SELECT with GROUP BY?

Question

I have an aggregated GROUP BY query that worked fine until I added the CASE statement. After adding that I get this error:

"Column 'gym.SalesDocumentItems.SalesDocumentItemStatusID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Here is the query:

select 
    a.AccountID, 
    isnull(sum(sdi.TotalAmount),0) AS Amount,
    isnull(sum(sdi.TotalDiscountAmount),0) AS Discount,
    isnull(sum(sdi.TaxAmount),0) AS TaxAmount, 
    isnull(sum(sdi.TaxDiscountAmount),0) AS TaxDiscountAmount, 
    isnull(sum(sdi.AmountPaid),0) AS AmountPaid,
    isnull(a.CreditAmountAvailable,0) * -1 AS Credit,
    CASE WHEN sdi.SalesDocumentItemStatusID IN(1,3) THEN
        isnull(sum(sdi.TotalAmount),0) - isnull(sum(sdi.TotalDiscountAmount),0) + isnull(sum(sdi.TaxAmount),0) - isnull(sum(sdi.TaxDiscountAmount),0) - isnull(sum(sdi.AmountPaid),0) - isnull(a.CreditAmountAvailable,0)
    ELSE
        -isnull(a.CreditAmountAvailable,0)
    END
    AS Balance,
    r.RunningBalanceTotal,
    isnull(sum(sdi.TotalAmount),0) - isnull(sum(sdi.TotalDiscountAmount),0) + isnull(sum(sdi.TaxAmount),0) - isnull(sum(sdi.TaxDiscountAmount),0) - isnull(sum(sdi.AmountPaid),0) - isnull(a.CreditAmountAvailable,0) - r.RunningBalanceTotal AS Difference,
    isnull(a.CreditAmountAvailable,0) AS AccountCredit
from gym.Account a
join gym.SalesDocument sd
on a.AccountID = sd.AccountID
join gym.SalesDocumentItems sdi
on sd.salesdocumentid = sdi.salesdocumentid
join RunningBalanceTotals r
on a.AccountID = r.AccountID
group by a.AccountID, a.CreditAmountAvailable, r.RunningBalanceTotal

I'm not sure how to achieve this. I need the case statement so that the select behaves differently for different circumstances. For any record in which sdi.SalesDocumentItemStatusID IN(1,3), I need the the full calculation; otherwise I merely need the negative of CreditAmountAvailable.

How can I achieve this? I am using MS SQL Server 2012.

[[EDIT]]

Here is a modified query based on Frank's query below:

WITH ungrouped as (
    SELECT a.AccountID, 
           sdi.TotalAmount,
           CASE WHEN sdi.SalesDocumentItemStatusID IN(1,3) THEN 1 ELSE 2 END AS method,
           sdi.TaxAmount,
           sdi.TotalDiscountAmount,
           sdi.TaxDiscountAmount,
           sdi.AmountPaid,
           a.CreditAmountAvailable,
           r.RunningBalanceTotal
      from gym.Account a
      join gym.SalesDocument sd
      on a.AccountID = sd.AccountID
      join gym.SalesDocumentItems sdi
      on sd.salesdocumentid = sdi.salesdocumentid
      join RunningBalanceTotals r
      on a.AccountID = r.AccountID
)
SELECT ungrouped.AccountID, 
       CASE WHEN ungrouped.method = 1 THEN
            isnull(sum(ungrouped.TotalAmount),0) - isnull(sum(ungrouped.TotalDiscountAmount),0) + isnull(sum(ungrouped.TaxAmount),0) - isnull(sum(ungrouped.TaxDiscountAmount),0) - isnull(sum(ungrouped.AmountPaid),0) - isnull(ungrouped.CreditAmountAvailable,0)
       ELSE
            -isnull(ungrouped.CreditAmountAvailable,0)
       END AS Balance,
       ungrouped.RunningBalanceTotal,
       isnull(sum(ungrouped.TotalAmount),0) - isnull(sum(ungrouped.TotalDiscountAmount),0) + isnull(sum(ungrouped.TaxAmount),0) - isnull(sum(ungrouped.TaxDiscountAmount),0) - isnull(sum(ungrouped.AmountPaid),0) - isnull(ungrouped.CreditAmountAvailable,0) - ungrouped.RunningBalanceTotal AS Difference,
       isnull(ungrouped.CreditAmountAvailable,0) AS Credit
  FROM ungrouped
  where ungrouped.AccountID IN (    4238534,    4231337,    4132170, 4100923, 4137728, 4143255,     4230150, 4238565 
  )
GROUP BY ungrouped.AccountID, ungrouped.CreditAmountAvailable, ungrouped.RunningBalanceTotal, ungrouped.method
ORDER BY ungrouped.AccountID

Answer 1

Looking at the comments to your original question, I see the requirement is to use a zero in the summation if the sdi.SalesDocumentItemStatusID is different from 1 or 3. This can be handled by dragging the CASE into the summation:

select 
    a.AccountID, 
    isnull(sum(sdi.TotalAmount),0) AS Amount,
    isnull(sum(sdi.TotalDiscountAmount),0) AS Discount,
    isnull(sum(sdi.TaxAmount),0) AS TaxAmount, 
    isnull(sum(sdi.TaxDiscountAmount),0) AS TaxDiscountAmount, 
    isnull(sum(sdi.AmountPaid),0) AS AmountPaid,
    isnull(a.CreditAmountAvailable,0) * -1 AS Credit,

      isnull(sum(CASE WHEN sdi.SalesDocumentItemStatusID IN(1,3) THEN sdi.TotalAmount ELSE 0 END),0)
    - isnull(sum(CASE WHEN sdi.SalesDocumentItemStatusID IN(1,3) THEN sdi.TotalDiscountAmount ELSE 0 END),0)
    + isnull(sum(CASE WHEN sdi.SalesDocumentItemStatusID IN(1,3) THEN sdi.TaxAmount ELSE 0 END),0)
    - isnull(sum(CASE WHEN sdi.SalesDocumentItemStatusID IN(1,3) THEN sdi.TaxDiscountAmount ELSE 0 END),0)
    - isnull(sum(CASE WHEN sdi.SalesDocumentItemStatusID IN(1,3) THEN sdi.AmountPaid ELSE 0 END),0)
    - isnull(a.CreditAmountAvailable,0)
    AS Balance,

    r.RunningBalanceTotal,
    isnull(sum(sdi.TotalAmount),0) - isnull(sum(sdi.TotalDiscountAmount),0) + isnull(sum(sdi.TaxAmount),0) - isnull(sum(sdi.TaxDiscountAmount),0) - isnull(sum(sdi.AmountPaid),0) - isnull(a.CreditAmountAvailable,0) - r.RunningBalanceTotal AS Difference,
    isnull(a.CreditAmountAvailable,0) AS AccountCredit
from gym.Account a
join gym.SalesDocument sd
on a.AccountID = sd.AccountID
join gym.SalesDocumentItems sdi
on sd.salesdocumentid = sdi.salesdocumentid
join RunningBalanceTotals r
on a.AccountID = r.AccountID
group by a.AccountID, a.CreditAmountAvailable, r.RunningBalanceTotal

Answer 2

First fetch all your columns and include the first part of the CASE logic, but no grouping into a CTE, which I called ungrouped. Then, do the grouping and include the result of the CASE of the CTE in the GROUP BY:

    WITH ungrouped as (
    SELECT a.AccountID, 
           sdi.TotalAmount,
           CASE WHEN sdi.SalesDocumentItemStatusID IN(1,3) THEN 1 ELSE 2 END AS method,
           sdi.TaxAmount,
           sdi.TotalDiscountAmount,
           sdi.TaxDiscountAmount,
           sdi.AmountPaid,
           a.CreditAmountAvailable,
           r.RunningBalanceTotal
      from gym.Account a
      join gym.SalesDocument sd
      on a.AccountID = sd.AccountID
      join gym.SalesDocumentItems sdi
      on sd.salesdocumentid = sdi.salesdocumentid
      join RunningBalanceTotals r
      on a.AccountID = r.AccountID
)
SELECT ungrouped.AccountID, 
       isnull(sum(ungrouped.TotalAmount),0) AS Amount,
       isnull(sum(ungrouped.TotalDiscountAmount),0) AS Discount,
       isnull(sum(ungrouped.TaxAmount),0) AS TaxAmount, 
       isnull(sum(ungrouped.TaxDiscountAmount),0) AS TaxDiscountAmount, 
       isnull(sum(ungrouped.AmountPaid),0) AS AmountPaid,
       isnull(ungrouped.CreditAmountAvailable,0) * -1 AS Credit,
       CASE WHEN ungrouped.method = 1 THEN
            isnull(sum(ungrouped.TotalAmount),0) - isnull(sum(ungrouped.TotalDiscountAmount),0) + isnull(sum(ungrouped.TaxAmount),0) - isnull(sum(ungrouped.TaxDiscountAmount),0) - isnull(sum(ungrouped.AmountPaid),0) - isnull(ungrouped.CreditAmountAvailable,0)
            ELSE
                -isnull(ungrouped.CreditAmountAvailable,0)
       END AS Balance,
       ungrouped.RunningBalanceTotal,
       isnull(sum(ungrouped.TotalAmount),0) - isnull(sum(ungrouped.TotalDiscountAmount),0) + isnull(sum(ungrouped.TaxAmount),0) - isnull(sum(ungrouped.TaxDiscountAmount),0) - isnull(sum(ungrouped.AmountPaid),0) - isnull(ungrouped.CreditAmountAvailable,0) - ungrouped.RunningBalanceTotal AS Difference,
       isnull(ungrouped.CreditAmountAvailable,0) AS AccountCredit
  FROM ungrouped
GROUP BY ungrouped.AccountID, ungrouped.CreditAmountAvailable, ungrouped.RunningBalanceTotal, ungrouped.method