CODEWITHSUNDEEP
sqlsql-servercase
Whiteneon
Whiteneon

Reputation: 35

IF or CASE statement with sum

Thats my table:

| ID | Value | Unit | 
|----|-------|------| 
| 1  | 5     | t    | 
| 1  | 700   | t    | 
| 1  | 612   | kg   |

I have a short questions on if and case statements in SQL Server.

This is my code:

(SELECT ISNULL(SUM(t.Value),0)
 FROM test t
 WHERE t.ID= "1") AS Result

The problem is if i have a specific value in a column i need to multiply the value befor the sum operator. If not i need just the result i get above.

My first try is:

SELECT Result = CASE WHEN t.Unit= 'kg' THEN (IsNULL(SUM(t.Value),0) * 1000)
ELSE IsNULL(SUM(v.Verbrauch),0)
END
FRFROM test t
WHERE t.ID= "1"

I hope this make the problem clear.

Any ideas how I can do this?

Upvotes: 0

Views: 614

Answers (2)

forpas
forpas

Reputation: 164069

I assume that 't' is for metric ton and you are trying to calculate the sum by converting tons to kgs, so you should multiply by 1000 when Unit = 't': 

SELECT COALESCE(SUM(Value * CASE WHEN Unit = 't' THEN 1000 ELSE 1 END), 0) as Result
FROM test 
WHERE ID = 1

This will return the sum in kgs.
If you want the sum in tons:

SELECT COALESCE(SUM(Value / CASE WHEN Unit = 'kg' THEN 1000.0 ELSE 1.0 END), 0) as Result
FROM test 
WHERE ID = 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269543

If I understand correctly, you want the case as an argument to the sum():

SELECT SUM(CASE WHEN t.Unit = 'kg' THEN 1000 * t.Value
                WHEN t.Unit = 't' THEN t.Value
           END) as 
FROM test t
WHERE t.ID = 1

Upvotes: 2

Related Questions