Why must you GROUP BY the columns in a CASE statement?

Question

I'm trying to run a query in SQL Server 2008 against a table where some of the data was entered inconsistently and I have to handle this.

Table data example:

OrderID   Qty   Price   MarkedUpTotal
1         10    1.00    11.00
1         -1    1.00    -1.10
1         -1    1.00    1.10

I have to handle the situation where the Qty is negative but the MarkedUpTotal was entered as positive.

I'd like to run the following query:

SELECT OrderID, SUM(Qty) as OrderTotalQty, 
        SUM(Qty*Price) as InternalCost,
        CASE WHEN Qty < 0 and MarkedUpTotal > 0 
             THEN sum(-1*MarkedUpTotal) 
             ELSE SUM(MarkedUpTotal) END as ClientCost  
    FROM OrderItems 
    GROUP BY OrderID

However, I get the following error when I run this query:

Column Qty is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column MarkedUpTotal is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I desire the following result:

OrderID    OrderTotalQty   InternalCost   ClientCost
1          8               8.00           8.80

It seems odd to me that I must GROUP BY Qty and MarkedUpTotal when they are only being used conditionally by the CASE statement. If I remove the last selection (the CASE statement) the query executes fine and does not require Qty or Price to be in the GROUP BY.

Why does SQL require this? Is there a single query that could accomplish the above?

Currently I'm resolving the issue by using a temp table. I modify each entry's MarkedUpTotal if needed and then do a simple SUM(MarkedUpTotal) in the main query from the temp table.

Answer 1

SELECT OrderID, SUM(Qty) as OrderTotalQty, 
        SUM(Qty*Price) as InternalCost,
        SUM(CASE WHEN Qty < 0 and MarkedUpTotal > 0 
             THEN -1*MarkedUpTotal
             ELSE MarkedUpTotal) END as ClientCost  
    FROM OrderItems 
    GROUP BY OrderID

The reason it gives error is because, you are SUMming it up inside the CASE - which will return 1 value outside. To the SELECT with GROUP BY, it will look like you are passing in a numeric value (which could be a constant or comes from some other source) as a column.

Think of your SQL Statement, similar to this

SELECT OrderID, SUM(Qty) as OrderTotalQty, 
        SUM(Qty*Price) as InternalCost,
        CASE WHEN Qty < 0 and MarkedUpTotal > 0 
             THEN 10
             ELSE 20 END as ClientCost  
    FROM OrderItems 
    GROUP BY OrderID

Now this is returning a new column (ClientCost), which is not using any aggregation.
So, it asks you to use that in GROUP BY expression.