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
 General SQL Server Forums
 New to SQL Server Programming
 Rounding, convert to percent
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

theKid27
Starting Member

21 Posts

Posted - 09/26/2013 :  09:14:13  Show Profile  Reply with Quote
Hi Experts/Members,

I have a question bout rounding and converting to percentage and adding in the '%'

This is my original code

SELECT * FROM
( SELECT Baby,
CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) as BabyValue
FROM NewBorn
WHERE Category = 'Boy'
AND TotalParent > 0
GROUP BY size
)returnResult
PIVOT ( SUM(BabyValue)FOR size IN ( [A],[B], [C],[D] ) ) AS BabypivotResult

The output is as below:

A B C D
0.0022 0.0029 0.0011 0.0032



I need to change it to percentage so i put *100 as below, so I get the data as below

CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) * 100 as BabyValue

A B C D
0.2200 0.2900 0.1100 0.3200




But I also need my data to have the '%' and it should have 2 decimal place which is as below, I have tried to make it this way

CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) * 100 + '%' as BabyValue

But it prompt me the error 'Error converting data type varchar to numeric.'

A B C D
0.22% 0.29% 0.11% 0.32%


Appreciate if anyone of you could advise me or share me some ideas.

Thank you.





James K
Flowing Fount of Yak Knowledge

3759 Posts

Posted - 09/26/2013 :  09:36:26  Show Profile  Reply with Quote
If you want to append a percent symbol to the end of a numeric quantity, you have to convert the numeric quantity to be of char or varchar type.

Usually this is not advisable to do this type of thing in the database. It is much better to do it in the presentation layer such as reporting services or a GUI where you are consuming the data if you have one.

If you absolutely must do it in SQL, then convert it to varchar using cast, convert or:
STR( 100.0* SUM(TotalBaby)/SUM(TotalParent) ,10,2)+'%'

Edited by - James K on 09/26/2013 09:36:50
Go to Top of Page

theKid27
Starting Member

21 Posts

Posted - 09/26/2013 :  18:54:16  Show Profile  Reply with Quote
Thanks James K for the advice, I'll put it at the GUI.
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.05 seconds. Powered By: Snitz Forums 2000