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 percentagedeclare @Sales decimal, @NoVisits int, @PerSales decimalselect @Sales = 3select @NoVisits = 1553select @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 resultthanks |
|
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 intselect @Sales = 3, @NoVisits = 1553select ltrim(str(100.0 * @Sales / @NoVisits, 6, 2))Peter LarssonHelsingborg, Sweden |
|
|
billsack
Starting Member
35 Posts |
Posted - 2007-06-25 : 08:54:38
|
Just do:(Sales*100) / numberofvisitsWorks 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 usedMadhivananFailing 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 doingset @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.aspxIn 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 overflowDECLARE @x DECIMAL(5,2);SET @x = 123.45; -- OKSET @x = 123.4567 -- OK, will be rounded to 123.46SET @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). |
|
|
|