| Author |
Topic |
|
RaghaSM
Yak Posting Veteran
52 Posts |
 Posted - 2008-07-14 : 14:04:08
|
| Hi All,I have table with A,B,C as three columns and whicha re of type integer when ever i say select a, b,c ,( b/a*100) as percentage from table1 I am getting 0 in the percentages But when I say slelect a,b,c, cast( b as decimal(10,2))/a*100 as percentages from table1 I am getting 10.0000000000000 as value, which i s right but I wanto have only 2 digits after decimal .What should i Do? Can anyone let me know.. why its not calculating percentages if I dont cast ?? Also What if I want the values of percentages in integer alng with % symbol.????Your help will be very much appreciated.Thnaks,Ragha |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2008-07-14 : 14:07:12
|
| Multiply by 100.0 instead of 100. When SQL divides and integer by an integer, it returns an integer.SELECT a,b,c, (b*100.0) / aJim |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-14 : 14:10:20
|
| the reason is this:-http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx |
|
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-07-14 : 14:11:38
|
quote:
Originally posted by jimf
Multiply by 100.0 instead of 100. When SQL divides and integer by an integer, it returns an integer.SELECT a,b,c, (b*100.0) / aJim
Thnaks its working.. But what if I want to include a percentage symbol along with that..If i say this...select a as Total ,b as completed , b*100/a +'%' from table1 its giving me a n error that Syntax error converting the varchar value '%' to a column of data type int.Can u please tell me where I am going wrong??Once again thanks for ur help |
|
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-14 : 14:16:45
|
quote:
Originally posted by RaghaSM
quote:
Originally posted by jimf
Multiply by 100.0 instead of 100. When SQL divides and integer by an integer, it returns an integer.SELECT a,b,c, (b*100.0) / aJim
Thnaks its working.. But what if I want to include a percentage symbol along with that..If i say this...select a as Total ,b as completed , b*100/a +'%' from table1 its giving me a n error that Syntax error converting the varchar value '%' to a column of data type int.Can u please tell me where I am going wrong??Once again thanks for ur help
why are you trying to concat % to result? You cant do this unless you cast the result onto string type. But you shouldnt do this if your intention is for displaying result as %. Display formating needs to be done at your front end application where you've functions extensively for this. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2008-07-14 : 14:17:04
|
| SELECT a,b,c, STR((b*100.0) / a,6,2) + '%'. You can't '+' strings and numbers, so you have to change the number to a string SELECT '123' + '456' = '123456'SELECT 123 + 456 = 579SELECT '123' + 456 doesn't workJim |
|
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-07-14 : 14:22:29
|
| Thanks a lot for your great help. Now its very much clear to me .Also I have one more question if the 'a' value is 0 then how should we elimate such situations? any suggestions? Sorry for asking questions over and over |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-14 : 14:25:48
|
quote:
Originally posted by RaghaSM
Thanks a lot for your great help. Now its very much clear to me .Also I have one more question if the 'a' value is 0 then how should we elimate such situations? any suggestions? Sorry for asking questions over and over
use b/nullif(a,0) * 100 which makes result null when a is 0 |
|
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-07-14 : 14:35:21
|
| Once again ..Thank you very much |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-15 : 03:33:21
|
| nullif(a,0) is short form of case when a=0 then null else a endMadhivananFailing to plan is Planning to fail |
|
|
|
|
calculate percentages