How to incorporate a data type in a select stateme

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to incorporate a data type in a select stateme

Author

Topic

Rdaniel
Starting Member

3 Posts

Posted - 2013-01-16 : 02:08:39

I have a select statement to display some calculations and this is what I have down:

SELECT Title, ((Retail-Cost)/Cost)*100 AS “Profit %”
FROM Table1;

Everything looks OK except the numbers in the new Profit % column; there are way to many decimal points so I need some help on adding the data type NUM(2,0) to the statement. Can I add it to the above select statement and if yes how (please write the complete statement)? One thing that I thought about was using conditions...please see below. This is not in a right format and will not run. Please let me know if I am on the right path and the correct format.

SELECT Title, ((Retail-Cost)/Cost)*100 AS “Profit %”
WHERE Profit % NUM(5,0)
FROM Table1;

Thanks all :)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 02:19:25

Can you provide sample input and expected output?

If you want 381.1089040000 --> 381
use select CAST(((145.343-30.21)/30)*100.00 AS DEC(5,0))

--
Chandu

Rdaniel
Starting Member

3 Posts

Posted - 2013-01-16 : 02:35:18

the input stat. is :

SELECT Title, ((Retail-Cost)/Cost)*100 AS “Profit %”
FROM Table1;

and the output is:

TITLE Profit %
BODYBUILD 65.0666666666666666666666666666666666667
REVENGE MICKEY 54.9295774647887323943661971830985915493

what should my input be to show NUM(2,0):

TITLE Profit %
BODYBUILD 65
REVENGEMICKEY 54

Rdaniel
Starting Member

3 Posts

Posted - 2013-01-16 : 02:39:06

There are 2 columns Title & Profit % and the profit % column instead of 65.66666666666666 needs to be 65 only.

Thanks :)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 04:18:28

select CAST(65.66666666666666 AS INT)

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources