Why do you have GROUP BY columns in a CASE statement?

Question

I am trying to run a query in SQL Server 2008 against a table where some data was entered inconsistently, and I have to process this.

Example table data:

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

I need to handle a situation where the Qty value is negative, but MarkedUpTotal was entered as positive.

I would 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, when I run this request, I get the following error:

The column is not valid in the selection list because it is neither contained in the aggregate function nor in the GROUP BY clause.

The MarkedUpTotal column is not valid in the select list because it is not contained in the aggregate function or in the GROUP BY clause.

I need the following result:

OrderID    OrderTotalQty   InternalCost   ClientCost
1          8               8.00           8.80

, GROUP BY Qty MarkedUpTotal, CASE. ( CASE), , Qty Price GROUP BY.

SQL ? , ?

, . MarkedUpTotal , SUM (MarkedUpTotal) temp.

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

, , , CASE - 1 . SELECT GROUP BY , ( - ) .

SQL-,

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

(ClientCost), - .
, GROUP BY.

Answer 2

, GROUP BY , , .

CASE, , , ; (.. ) WHERE, CASE , SUM.

Answer 3

. Qty Sql?

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

:

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

:

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

, :)

Answer 4

:

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

, GROUP BY Qty MarkedUpTotal, CASE.

:

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

The SQL server does not know how to evaluate CASE WHEN Qty < 0 and MarkedUpTotal > 0. You have 3 different values ​​for these records in your table, which is usually not a problem, but since you group your records, the expression does not make sense, because the SQL server does not know which of the three values ​​to use in evaluating the case expression, therefore it throws non-user errors in OP.

All code suggestions in this thread provide a solution.