Register Blogs FAQ Members List Social Groups Calendar Search Today's Posts Mark Forums Read

Go Back   DeveloperBarn Forums > Databases > SQL Development
Reload this Page sQL select add '%' next to value

Sponsored Links

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.


Closed Thread
 
LinkBack (1) Thread Tools Display Modes
  1 links from elsewhere to this Post. Click to view. #1  
Old March 28th, 2008, 08:39 AM
Barn Frequenter
  
Join Date: Mar 2008
Posts: 194
Thanks: 16
Thanked 0 Times in 0 Posts
Rep Power: 1
peebman2000 is an unknown quantity at this point
Exclamation sQL select add '%' next to value
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
Sponsored Links
  #2  
Old March 28th, 2008, 08:43 AM
mehere's Avatar
Super Sarcasm Mistress
  
Join Date: Mar 2008
Location: Wide Awake In Dreamland
Posts: 160
Thanks: 12
Thanked 29 Times in 27 Posts
Rep Power: 1
mehere will become famous soon enough

Awards Showcase
Microsoft SQL Server Classic ASP 
Total Awards: 2

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

Comments on this post
jmurrayhead agrees:
__________________
Quote of the Month:
Quality: The race for quality has no finish line- so technically, it's more like a death march.

Questions to Ponder:
What do you do when you see an endangered animal eating an endangered plant?

iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
copyright © 2008 sbenj69
  #3  
Old March 28th, 2008, 08:44 AM
jmurrayhead's Avatar
The Barnfather
  
Join Date: Mar 2008
Location: Reston, VA, USA
Posts: 951
Thanks: 22
Thanked 93 Times in 90 Posts
Blog Entries: 6
Rep Power: 4
jmurrayhead is a jewel in the roughjmurrayhead is a jewel in the roughjmurrayhead is a jewel in the roughjmurrayhead is a jewel in the rough

Awards Showcase
Microsoft Windows Microsoft SQL Server Microsoft .Net Classic ASP 
Total Awards: 4

Default
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
If you agree with me... click the icon!
If my post solved your problem, click the button in the lower right-hand corner of the post.

If you like it here...throw us a few bones to help support us.

Join our Folding team: DeveloperBarn Folding
  #4  
Old March 28th, 2008, 09:53 AM
Barn Frequenter
  
Join Date: Mar 2008
Posts: 194
Thanks: 16
Thanked 0 Times in 0 Posts
Rep Power: 1
peebman2000 is an unknown quantity at this point
Default reply
Hey thanks mehere that worked. Thanks for the help.

Quote:
Originally Posted by mehere View Post
try this:
Code:
convert(varchar(25),round(cast((convert(float,implemented) * 100.00 /convert(float,total)) as float), 1)) + '%' as 'percent'
  #5  
Old March 28th, 2008, 09:55 AM
Barn Frequenter
  
Join Date: Mar 2008
Posts: 194
Thanks: 16
Thanked 0 Times in 0 Posts
Rep Power: 1
peebman2000 is an unknown quantity at this point
Thumbs up reply
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 View Post
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.
  #6  
Old March 28th, 2008, 10:03 AM
Barn Frequenter
  
Join Date: Mar 2008
Posts: 194
Thanks: 16
Thanked 0 Times in 0 Posts
Rep Power: 1
peebman2000 is an unknown quantity at this point
Thumbs up reply
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 View Post
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.
  #7  
Old March 28th, 2008, 10:10 AM
jmurrayhead's Avatar
The Barnfather
  
Join Date: Mar 2008
Location: Reston, VA, USA
Posts: 951
Thanks: 22
Thanked 93 Times in 90 Posts
Blog Entries: 6
Rep Power: 4
jmurrayhead is a jewel in the roughjmurrayhead is a jewel in the roughjmurrayhead is a jewel in the roughjmurrayhead is a jewel in the rough

Awards Showcase
Microsoft Windows Microsoft SQL Server Microsoft .Net Classic ASP 
Total Awards: 4

Default
Quote:
Originally Posted by peebman2000 View Post
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.

Comments on this post
peebman2000 agrees: thanks
  #8  
Old March 28th, 2008, 10:16 AM
Barn Frequenter
  
Join Date: Mar 2008
Posts: 194
Thanks: 16
Thanked 0 Times in 0 Posts
Rep Power: 1
peebman2000 is an unknown quantity at this point
Thumbs up reply
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 View Post
In this case you would want to treat it as text, so place {0} in the DataFormatString property.
  #9  
Old March 28th, 2008, 10:21 AM
jmurrayhead's Avatar
The Barnfather
  
Join Date: Mar 2008
Location: Reston, VA, USA
Posts: 951
Thanks: 22
Thanked 93 Times in 90 Posts
Blog Entries: 6
Rep Power: 4
jmurrayhead is a jewel in the roughjmurrayhead is a jewel in the roughjmurrayhead is a jewel in the roughjmurrayhead is a jewel in the rough

Awards Showcase
Microsoft Windows Microsoft SQL Server Microsoft .Net Classic ASP 
Total Awards: 4

Default
Glad we could help
Closed Thread

  DeveloperBarn Forums > Databases > SQL Development

Bookmarks

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump

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


All times are GMT -4. The time now is 09:14 AM.

Contact Us - DeveloperBarn.com - Top


Content Relevant URLs by vBSEO 3.2.0