CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Louis
Louis

Reputation: 19

Multiplication In SQL and get a derived column

 x      y       z
200     10     0
300     11     1
400     12     2
500     13     3
600     14     4
700     15     5

We need a derived column W with the following calculations made

if y = 10 then Col W=x
if y = 11 then Col W=x*333.3
if y = 12 then Col W=x*222.2 
if y = 13 then Col W=(X*999)*222.2
if y = 14 then Col W=x*Z
if y = 15 then Col W=(x*222.2)*Z

Upvotes: 1

Views: 148

Answers (3)

3BK
3BK

Reputation: 1348

You have already received a few valid responses, but I'm going to throw this up on the board just in case you want a different approach...

You could always add your calculated column to the table itself, as such:

CREATE TABLE myTable
(
     X INT
    ,Y INT
    ,Z INT
)
;

INSERT INTO myTable (X, Y, Z) VALUES (200, 10, 0);
INSERT INTO myTable (X, Y, Z) VALUES (300, 11, 1);
INSERT INTO myTable (X, Y, Z) VALUES (400, 12, 2);
INSERT INTO myTable (X, Y, Z) VALUES (500, 13, 3);
INSERT INTO myTable (X, Y, Z) VALUES (600, 14, 4);
INSERT INTO myTable (X, Y, Z) VALUES (700, 15, 5);

ALTER TABLE myTable ADD W AS 
CASE Y
    WHEN 10
        THEN X
    WHEN 11
        THEN X * 333.3
    WHEN 12
        THEN X * 222.2
    WHEN 13
        THEN (X * 999) * 222.2
    WHEN 14
        THEN X * Z
    WHEN 15
        THEN (X * 222.2) * Z
END
;

SELECT * FROM myTable
;

Returns:

X       Y       Z        W
-----------------------------
200     10      0        200
300     11      1        99990
400     12      2        88880
500     13      3        110988900
600     14      4        2400
700     15      5        777700

If, on the other hand, you only want this response one time (as a result to a specific query), then you already have the answer from a few others:

SELECT
     X
    ,Y
    ,Z
    CASE Y
        WHEN 10
            THEN X
        WHEN 11
            THEN X * 333.3
        WHEN 12
            THEN X * 222.2
        WHEN 13
            THEN (X * 999) * 222.2
        WHEN 14
            THEN X * Z
        WHEN 15
            THEN (X * 222.2) * Z
    END AS W
FROM myTable
;

Upvotes: 0

Javlon Ismatov
Javlon Ismatov

Reputation: 194

with table1 as(
select 200 'x',10 'y', 0 'z' union all
select 300, 11, 1 union all
select 400, 12, 2 union all
select 500, 13, 3 union all
select 600, 14, 4 union all
select 700, 15, 5)
select *,
case y  when 10 then x
        when 11 then x*333.3
        when 12 then x*222.2 
        when 13 then (x*999)*222.2
        when 14 then x*z
        when 15 then (x*222.2)*z end 'w' from table1

Upvotes: 0

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

Use case clause as next:-

declare @myTable table (x int , y int , z int)

insert into @myTable values ( 200,10,0)
insert into @myTable values ( 300,11,1)
insert into @myTable values ( 400,12,2)
insert into @myTable values ( 500,13,3)
insert into @myTable values ( 600,14,4)
insert into @myTable values ( 700,15,5)


select x , y , z , case
            when y = 10 then x
            when y = 11 then x*333.3
            when y = 12 then x*222.2 
            when y = 13 then (X*999)*222.2
            when y = 14 then x*Z
            when y = 15 then (x*222.2)*Z
            end as w

from @myTable

Upvotes: 3

Related Questions