• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1511
  • Last Modified:

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.
0
TECHADVICE
Asked:
TECHADVICE
  • 2
2 Solutions
 
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
 
ExistenceExistsCommented:
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
 
Guy Hengel [angelIII / a3]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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now