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

Bear in mind that the result of a GROUP BY statement, or of a statement where one or columns uses an aggregate function, has each row containing a summary of other rows.

The CASE expression you're using is dependent on values of individual rows rather than summaries; you may only reference non-aggregate values (i.e. single-row values) in an aggregate function, or in the WHERE clause, so the solution would involve placing your CASE inside an aggregate function, in this case your SUM.

Answer 2

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.

Answer 3

The error is about this part. What Qty row value should Sql Server use?

CASE WHEN Qty < 0 and MarkedUpTotal > 0 
    THEN sum(-1*MarkedUpTotal) 
    ELSE SUM(MarkedUpTotal) 
END as ClientCost

You could rewrite it like:

sum(CASE WHEN Qty < 0 and MarkedUpTotal > 0 
    THEN sum(-1*MarkedUpTotal) 
    ELSE SUM(MarkedUpTotal) 
END as ClientCost)

Or:

CASE WHEN sum(Qty) < 0 and sum(MarkedUpTotal) > 0 
    THEN sum(-1*MarkedUpTotal) 
    ELSE SUM(MarkedUpTotal) 
END as ClientCost

Depending on what you mean :)

Answer 4

Do it like this:

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

Answer 5

It seems odd to me that I must GROUP BY Qty and MarkedUpTotal when they are only being used conditionally by the CASE statement.

I've highlighted the error below:

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

SQL server doesn't know how to evaluate CASE WHEN Qty < 0 and MarkedUpTotal > 0. You have 3 different values for these records in your table, which is normally not a problem -- but since you're grouping your records, the expression is meaningless since SQL server doesn't know which of the three values to use when it evaluates the case expression, so it throws the user-unfriendly parse errors seen in the OP.

All of the code suggestions in this thread provide a solution.