calculate percentage & display decimal

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

SQL Server 2000 Forums

Transact-SQL (2000)

calculate percentage & display decimal

Author

Topic

jn-at-uk
Starting Member

20 Posts

Posted - 2005-04-29 : 05:15:52

I have 2 values & want to return the decimal as a percentage
declare @Sales decimal, @NoVisits int, @PerSales decimal
select @Sales = 3
select @NoVisits = 1553

select @PerSales = (@Sales/@NoVisits * 100)

On my calculator I get a result of 0.1931745009.
I want to display 0.19 % as my output.
When I run this in sql, I get 0 result.

How do i get 0.19 % as my result

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-29 : 05:16:41

select @PerSales = convert(decimal(18,2),(1.0*@Sales/@NoVisits * 100))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jn-at-uk
Starting Member

20 Posts

Posted - 2005-04-29 : 05:23:23

thanku.....worked

tinhtam71
Starting Member

1 Post

Posted - 2007-06-20 : 13:35:39

I have the same problem, and tried your solution but it still showing 0. Is there any kind of sql setup involved? Thanks.

Tinhtam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 13:40:56

declare @sales int,
@novisits int

select @Sales = 3,
@NoVisits = 1553

select ltrim(str(100.0 * @Sales / @NoVisits, 6, 2))

Peter Larsson
Helsingborg, Sweden

billsack
Starting Member

35 Posts

Posted - 2007-06-25 : 08:54:38

Just do:

(Sales*100) / numberofvisits

Works ok for me...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-25 : 09:01:23

quote:
Originally posted by tinhtam71

I have the same problem, and tried your solution but it still showing 0. Is there any kind of sql setup involved? Thanks.

Tinhtam

Post the code you used

Madhivanan

Failing to plan is Planning to fail

joeyc6401
Starting Member

1 Post

Posted - 2013-10-17 : 14:48:54

when you use the as decimal statement what does the number in parentheses mean? I have my query working and here is the line in the statement but I don't know what the 11,2 is doing

set @percent = (select CAST(@compliant as decimal(11,2))/@total * 100 as Compliance)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 16:14:51

Those numbers in the brackets are precision and scale. See here: http://msdn.microsoft.com/en-us/library/ms187746.aspx
In plain English, You can have at most 11 digits in the number, of which 2 are reserved for the fractional part. The remaining 9 are available for the integer part.

In the example below, the first two are valid; the second will be rounded off to two decimal digits. But the 3rd will result in an overflow

DECLARE @x DECIMAL(5,2);
SET @x = 123.45; -- OK
SET @x = 123.4567 -- OK, will be rounded to 123.46

SET @x = 1234; -- will cause overflow

It is recommended that you start a new thread when you want to ask a question rather than reply to an old thread (or even new unrelated thread).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources