SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 calculate percentage & display decimal
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jn-at-uk
Starting Member

20 Posts

Posted - 04/29/2005 :  05:15:52  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 04/29/2005 :  05:16:41  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

jn-at-uk
Starting Member

20 Posts

Posted - 04/29/2005 :  05:23:23  Show Profile  Reply with Quote
thanku.....worked
Go to Top of Page

tinhtam71
Starting Member

1 Posts

Posted - 06/20/2007 :  13:35:39  Show Profile  Reply with Quote
I have the same problem, and tried your solution but it still showing 0. Is there any kind of sql setup involved? Thanks.

Tinhtam
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 06/20/2007 :  13:40:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
declare @sales int,
@novisits int

select @Sales = 3,
@NoVisits = 1553

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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

billsack
Starting Member

35 Posts

Posted - 06/25/2007 :  08:54:38  Show Profile  Reply with Quote
Just do:

(Sales*100) / numberofvisits

Works ok for me...
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 06/25/2007 :  09:01:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

joeyc6401
Starting Member

1 Posts

Posted - 10/17/2013 :  14:48:54  Show Profile  Reply with Quote
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)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/17/2013 :  16:14:51  Show Profile  Reply with Quote
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).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000