How to select only first two characters in

[ausmoran]

You were all so helpful yesterday in putting together the following T-SQL statement. Now I am trying to continue and build from there, but this time I only want to use the first two characters of the contents of the LICENSE NUMBER field (in the member table). Can you guys rescue me once again?

Select Member.License_Number, Count(1) 'Count', convert(decimal(5, 2), null) as Percentage
Into #count
from Member,Member_Association
WHERE Member.Member_Number=Member_Association.Member_Number AND Member_Association.Status='a'
Group By License_Number
Order By License_Number Asc

update #count set Percentage = 100.0 * [count]/(select sum([count]) from #count)
select * from #count
drop table #count

THANKS AGAIN!!!!!!!!

 

[checkai]

left(fieldname,2)

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[ausmoran]

Thanks, Checkai! I tried several different approaches, however I can't seem to get the syntax right. Can you help me out....here's what I tried

Select (left(Member.License_Number,2), Count(1) 'Count', convert(decimal(5, 2), null) as Percentage
Into #count
from Member,Member_Association
WHERE Member.Member_Number=Member_Association.Member_Number AND Member_Association.Status='a'
Group By License_Number
Order By License_Number Asc

update #count set Percentage = 100.0 * [count]/(select sum([count]) from #count)
select * from #count
drop table #count

 

[gmmastros]

It looks like you have an extra open parenthesis. And since you are grouping, you must include the 'modified' field in the group by.

Select left(Member.License_Number,2), Count(1) 'Count', convert(decimal(5, 2), null) as Percentage
Into #count
from Member,Member_Association
WHERE Member.Member_Number=Member_Association.Member_Number AND Member_Association.Status='a'
Group By left(Member.License_Number,2)
Order By left(Member.License_Number,2) Asc

update #count set Percentage = 100.0 * [count]/(select sum([count]) from #count)
select * from #count
drop table #count

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ausmoran]

Thanks again for your very kind help! Unfortunately, I am now getting an error message that says,

Server: Msg 8155, Level 16, State 1, Line 1
No column was specified for column 1 of '#count'.

 

[gmmastros]

Sorry about that.

try this..

Select left(Member.License_Number,2) [red]As SomeFieldName[/red], Count(1) 'Count', convert(decimal(5, 2), null) as Percentage
Into #count
from Member,Member_Association
WHERE Member.Member_Number=Member_Association.Member_Number AND Member_Association.Status='a'
Group By left(Member.License_Number,2)
Order By left(Member.License_Number,2) Asc

update #count set Percentage = 100.0 * [count]/(select sum([count]) from #count)
select * from #count
drop table #count

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ausmoran]

Thank you so very much for your help. I am new to all of this and am having lots of trouble finding a good T-SQL reference manual. I can't tell you how much I appreciate your help!

-Austin

 

[jbenson001]

use the SQL Server Book On Line for help.