Add a column into a select for calculation

Question

I have 2 columns from a table and i want to add a third column to output the result of a calculation

select statement at the moment is:

select revenue, cost
from   costdata

my 2 columns are revenue and cost

table name: costdata

my formula is: = ((revenue - cost)/ revenue)*100

I want the third column to be named 'result'

any idea on how to do this in a select statement?

gvee · Answer 1 · 2014-02-06T14:03:55.510

SELECT revenue
     , cost
     , ((revenue - cost) / revenue) * 100 As result
FROM   costdata

You mentioned in the comments that you get a divide by zero error. This wll occur when revenue equals zero.

What you want to happen in this scenario is up to you but this should get you started

SELECT revenue
     , cost
     , CASE WHEN revenue = 0 THEN
         0
       ELSE
         ((revenue - cost) / revenue) * 100
       END As result 
FROM   costdata

score 1 · Answer 2 · answered Feb 06 '14 at 13:33

1

Try,

select revenue, cost,((revenue - cost)/ revenue)*100 AS result
from   costdata

answered Feb 06 '14 at 13:33

BAdmin

927
1
11
19

I think your 'revenue' returning 0 in some cases. You have to check that. – BAdmin Feb 06 '14 at 13:39
You can use Select dividend / nullif(divisor, 0) source: http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql – BAdmin Feb 06 '14 at 13:42

score 1 · Answer 3 · edited Feb 06 '14 at 13:39

1

SELECT revenue, cost, ((revenue - cost)/ revenue)*100 AS result FROM costdata

edited Feb 06 '14 at 13:39

Prahalad Gaggar

11,389
16
53
71

answered Feb 06 '14 at 13:35

user3199248

7
1

score 1 · Accepted Answer · answered Feb 06 '14 at 13:37

1

Query:

SELECT revenue, 
       cost,
       CASE WHEN revenue <> 0 
            THEN ((revenue - cost) / revenue) * 100
       ELSE 0 END As result
FROM   costdata

answered Feb 06 '14 at 13:37

Justin

9,634
6
35
47

if revenue is decimal or float then its ok else you have to cast it into decimal to get exact result. – KumarHarsh Feb 06 '14 at 15:02

Add a column into a select for calculation

4 Answers4