Help with SUM and CASE WHEN for order totals

Transact-SQL
1

Hello. I am new on here and I am needing some help with this SUM and CASE WHEN. Probably easy but been trying for a while and cant seem to get it working how I want. Here is an example.

select
order number,
case when item description like X
then 2 else 1 end as adj case count,
adj case count (multiplied by) order qty = total case
sum (total case)
from -----
group by (order number....)

both the item description and order quantity are fields in the table but do not care too much about the data. Just need to see the order number matched up with the total case. Hope that makes sense and any help would be appreciated!

2

That is not a valid query as written - not sure what you are looking for. Please provide sample data as create/insert statements and expected results from that sample data.

For code - place 3 back-ticks in the editor on the line before the code and the line following the code. That will get you something like this:

-- This is sample code
Declare @testData Table (OrderNumber int, ItemDescription varchar(100), OrderQuantity int, ...)
 Insert Into @testData (OrderNumber, ItemDescription, OrderQuantity)
 Values (1, 'one', 1), (2, 'two', 2), ...;

 Select *
  From @testData;
3

I appreciate the reply. I wasn't very clear and was trying to send from my phone. Let me rephrase.
I currently have a table “ORDER”. Table has:
Order Number. Item Description. Order Quantity.
12345. Apple. 2
12345. Banana. 2
12345. Banana. 3
12345. Orange. 4
67890. Orange. 3
67890. Banana. 3
67890. Banana. 2
67890. Apple. 4

SELECT
[ORDER NUMBER],
CASE WHEN [ITEM DESCRIPTION]
LIKE ‘APPLE’ THEN 2
ELSE 1
END AS ‘ADJ CASE COUNT’,
SUM([ADJ CASE COUNT] * [ORDER QUANTITY]) AS ‘TOTAL CASE’,
FROM ORDER
GROUP BY [ORDER NUMBER]

So my expected result would be
Order Number. Total Case.
12345 13
67890. 16

Because on 12345:
Orange and Banana each count as one and total ordered was 9. Each apple counts as 2 and total ordered was 2. 2 * 2= 4. 9 + 4 = 13 total for 12345

4 
Declare @Order table (OrderNumber int, ItemDescription varchar(100), OrderQuantity int);
 Insert Into @Order (OrderNumber, ItemDescription, OrderQuantity)
 Values (12345, 'Apple', 2)
      , (12345, 'Banana', 2)
      , (12345, 'Banana', 3)
      , (12345, 'Orange', 4)
      , (67890, 'Orange', 3)
      , (67890, 'Banana', 3)
      , (67890, 'Banana', 2)
      , (67890, 'Apple', 4);


 Select o.OrderNumber
      , TotalCase = sum(o.OrderQuantity * iif(o.ItemDescription = 'Apple', 2, 1))
   From @Order                  o
  Group By
        o.OrderNumber;
5

Ah. Didn't think about the IF function. Thanks for your help.

6

IIF is just short-hand for a CASE expression, it could also be written as: sum(o.OrderQuantity * CASE WHEN o.ItemDescription = 'Apple' THEN 2 ELSE 1 END)

The key is to multiply the quantity by 2 for the item 'Apple' - and take the original quantity for all other items.