Rounding, convert to percent - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Rounding, convert to percent

Author

Topic

theKid27
Starting Member

21 Posts

Posted - 2013-09-26 : 09:14:13

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-26 : 09:36:26

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)+'%'

theKid27
Starting Member

21 Posts

Posted - 2013-09-26 : 18:54:16

Thanks James K for the advice, I'll put it at the GUI.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources