Discuss "sQL select add '%' next to value" in the SQL Development forum.

peebman2000 · #1 (**permalink**) March 28th, 2008, 08:39 AM

Hello everyone, peebman2000 semi-beginner programmer. I have a quick question for one of my sql queries i'm using for a .net app.

Below is my query they returns the count in some columns as well as the percent. When you look at the select as percent is my percent field. I want to show a '%' after return percent value.

Currently it returns as 7.9, but I want it to return as 7.9%. I tried just doing + '%' but that didn't work.

Can anyone give me the solution on using this query and adding the '%' at the end of my return results.

I appreciate any help, thanks.

sql query:

Code:

Select *, round(cast((convert(float,implemented) * 100.00 /convert(float,total)) as float), 1) as 'percent' From(
Select count(id) as total, sum(moreinfo) as More_info, sum(assigned) as Inprocess_team,
sum(assigned_expert) as Inprocess_expert, sum(statewide) as Statewide, sum(outscope) as OutOfscope,
sum(notimplemented) as NotImplmntd, sum(implemented) as Implemented,  Agency 
from (Select	id, 
			case when step = 'GETTING MORE INFORMATION' then
				count(step) else 0 end as 'moreinfo',
			case when step = 'IN PROCESS-ASSIGNED TO A SUBJECT MATTER EXPERT' then
				count(step) else 0 end as 'assigned_expert',
			case when step = 'REFERRED TO STATEWIDE ISSUES TEAM' then
				count(step) else 0 end as 'statewide',
			case when step = 'NOT WITHIN THE SCOPE OF THIS PROGRAM' then
				count(step) else 0 end as 'outscope',
			case when step = 'NOT IMPLEMENTED' then
				count(step) else 0 end as 'notimplemented',
			case when step ='IMPLEMENTED' then
				count(step) else 0 end as 'implemented',
			case when step = 'IN PROCESS-ASSIGNED TO A TEAM' then
				count(step) else 0 end as 'assigned',
			agency
		from efficiency 
		where time >= @DateFrom AND time < DATEADD(day, 1, @DateTo)
		group by id, step, agency) as new
group by agency) as anothertable
where agency = @agency
order by agency

mehere · #2 (**permalink**) March 28th, 2008, 08:43 AM

try this:

Code:

convert(varchar(25),round(cast((convert(float,implemented) * 100.00 /convert(float,total)) as float), 1)) + '%' as 'percent'

jmurrayhead · #3 (**permalink**) March 28th, 2008, 08:44 AM

I would say the best way to handle this is to format the string after the database outputs it to the application. If this is being returned to a GridView, for example, you can use the DataFormatString property to format the string into a percentage.

peebman2000 · #4 (**permalink**) March 28th, 2008, 09:53 AM

Hey thanks mehere that worked. Thanks for the help.

Quote:

Originally Posted by mehere

try this:

Code:

convert(varchar(25),round(cast((convert(float,implemented) * 100.00 /convert(float,total)) as float), 1)) + '%' as 'percent'

peebman2000 · #5 (**permalink**) March 28th, 2008, 09:55 AM

Thanks for the reply jmurryhead, I have displayed in a gridview, but the results show as 7.90 and I tried changing the dataformatstring to {0.p1} I also tried {0.#%} and it still wouldn't give me 7.9% it would give me7.90%.

Anyway I tried mehere way and that worked, so I guess i'll just do it that way.

Quote:

Originally Posted by jmurrayhead

I would say the best way to handle this is to format the string after the database outputs it to the application. If this is being returned to a GridView, for example, you can use the DataFormatString property to format the string into a percentage.

peebman2000 · #6 (**permalink**) March 28th, 2008, 10:03 AM

jmurrayhead, I tested the query in sql query editor and it showed the % sign like I want, but when I put it in the gridview it doesn't show the % sign.
Which dataformatstring could I use to show % in the gridview?

Quote:

Originally Posted by jmurrayhead

I would say the best way to handle this is to format the string after the database outputs it to the application. If this is being returned to a GridView, for example, you can use the DataFormatString property to format the string into a percentage.

jmurrayhead · #7 (**permalink**) March 28th, 2008, 10:10 AM

Quote:

Originally Posted by peebman2000

jmurrayhead, I tested the query in sql query editor and it showed the % sign like I want, but when I put it in the gridview it doesn't show the % sign.
Which dataformatstring could I use to show % in the gridview?

In this case you would want to treat it as text, so place {0} in the DataFormatString property.

peebman2000 · #8 (**permalink**) March 28th, 2008, 10:16 AM

Thanks Jmurrayhead, that work but I didn't realize I didn't update the right datsource. So when I was testing it in the gridview I was using the wrong datasource. But your suggestion worked in the gridview as well as Meheres.

I tried it in the gridview with and without the dataformatstring and it worked.

Thanks for your help again.

Quote:

Originally Posted by jmurrayhead

In this case you would want to treat it as text, so place {0} in the DataFormatString property.

jmurrayhead · #9 (**permalink**) March 28th, 2008, 10:21 AM

Glad we could help

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.developerbarn.com/sql-development/80-sql-select-add-next-value.html
Posted By	For	Type	Date
DeveloperBarn Forums - ASP Help, ASP.Net Help, PHP Help, SQL Help, Tutorials, Windows Help	This thread	Refback	March 28th, 2008 09:24 AM

Discuss "sQL select add '%' next to value" in the SQL Development forum.

SQL Development - Structured Query Language (SQL) is the talk of databases. Discuss topics such as joins, triggers and other advanced SQL topics.