Formula in SQL query

Question

Post reply

Formula in SQL query

pankaj-426531

Ten Centuries

Points: 1123
More actions
November 14, 2007 at 3:16 am

#200444

hi,
I want to create a query with a formula to compute a percentage.
The query(illustrative not actual) is like
Select income, expenses from sales
What i want is to have a column which contains ((income-espenses)/income)*100 i.e a percentage value in the same query using the other columns.
I tried using
select income, expense, ((income-expense)/income)*100 as percentageexpense from sales
but this is not working.
Can someone help?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Fracn Old Hand Points: 389 More actions · Answer 1

What kind of data type is income? And expense?

If they are decimal data type, your query works.

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 2

What error are you receiving?

K. Brian Kelley
@kbriankelley

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 3

And Divide By Zero errors can kill your query if you don't use Try..Catch or some other test for income being zero, so make sure to take that into account when coding.

Also, if you're just looking to throw a percent sign at the end of the result, cast/convert the formula as a String and concatenate the percent sign.

Convert(char(15),((Income-Expense)/Income) * 100) + '%'

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

If income and expense are integers, it won't work unless you force a conversion as in the following...

select income, expense, ((income-expense)/income)*100.0 as percentageexpense from sales

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Jeff, you have to force the conversion to numeric before the division operation.

This code shows that with a check for divide by zero also.

select

income,

expense,

[Percent] = ((income-expense+0.00)/nullif(income,0))*100.0

from

(

-- Test Data

select income =4, expense =3 union all

select income =0, expense =3

) a

Results:

income expense Percent

----------- ----------- -------------------------------

4 3 25.00000000000000

0 3 NULL

(2 row(s) affected)

Bhavani Taninki SSC Journeyman Points: 83 More actions · Answer 6

Hi

Select

Income,

Expense,

(

(Cast(Income as Float) - Cast(Expense as Float)) / Cast( ISNULL(NULLIF(Income,0),1) as Float)

) * 100 As percentageexpense

FROM

(

select income =4, expense =3

union all

select income =0, expense =3

) a

Bhavani.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Bhavani Taninki (11/21/2007)
Hi
Select
Income,
Expense,
(
(Cast(Income as Float) - Cast(Expense as Float)) / Cast( ISNULL(NULLIF(Income,0),1) as Float)
) * 100 As percentageexpense
FROM
(
select income =4, expense =3
union all
select income =0, expense =3
) a
Bhavani.

I don't see why you are doing this:

ISNULL(NULLIF(Income,0),1)

For the second row, it returns a value of -300, something that is obviously wrong. The percentage in this case just does not make sense if the income is zero, so NULL is a better answer.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 8

Your problem may with the the data types. SQL Server is not very friendly with divides of INT / INT.

This will return 0

DECLARE

@Expense INT

, @Income INT

SELECT @Expense = 100, @Income = 200

SELECT @Expense / @Income

This will return a proper value

SELECT CAST(@Expense AS MONEY) / @Income

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Jeff, you have to force the conversion to numeric before the division operation

Thanks, for the catch, Michael... more coffee! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)