Multiply two variables then divide by third variable in same table

Given the following Table
ID  PERIOD     DATA1  DATA2   DATA3
A  20071001    32           4             23
B  20071001    22          10            5
C  20071001    46          12            15

How can I run a select statement that would
DATA1*DATA2 = DATA4
DATA4/DATA3 = DATA5
All in the same select statement

Desired results would be:
ID  PERIOD      DATA4    DATA5
A  20071001     128        5.57
B  20071001      220      44.00
C  20071001      552      36.80

Also, I would need the statement to account for divide by zero issues.
TECHADVICEAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
in sql server, you cannot use a column alias directly.

you could do this:

select id, perdio, data4, data4/data3 as data5
from (
select id, period, data1*data2 as data4, (data1*data2) / data3 as data5
from yourtable
) as l
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select id, period, data1*data2 as data4, (data1*data2) / data3 as data5
from yourtable
0
 
TECHADVICEAuthor Commented:
is it possible to amend similar to the following:
select id, period, data1*data2 as data4, data4/data3 as data5
from yourtable
0
 
ExistenceExistsConnect With a Mentor Commented:
In order to account for division by zero, try this:

select
id,
period,
data1*data2 as data4,
case data3 when 0 then 0 else data4 / data3 end as data5
from yourtable
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.