| Author |
 Topic  |
|
|
praniktha
Starting Member
3 Posts |
 Posted - 09/20/2012 : 06:06:56
|
Hi,
I have a column in my Table which contains
AS,Bs
AS,CS
CS,ES
DE,AS
IR,CS,DE.
I need to find the Count of each value and output shuld come like this
AS 5
BS 3
CS 4 .can anyone help me to write a Procedure |
|
|
senthil_nagore
Aged Yak Warrior
India
997 Posts |
Posted - 09/20/2012 : 06:10:04
|
What about other values ES, IR , DE ? or else you want the count of only AS,BS and CS ?
Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008 |
 |
|
|
praniktha
Starting Member
3 Posts |
Posted - 09/20/2012 : 06:17:12
|
| yes i want count of other values also ,the values may be of different combination & diff values |
|
|
|
senthil_nagore
Aged Yak Warrior
India
997 Posts |
Posted - 09/20/2012 : 07:03:04
|
Assign all the records into a varaible as below and try this
declare @my_var varchar(max)
Declare @srt varchar(10)
set @my_var = 'AS,Bs,AS,CS,CS,ES,DE,AS,IR,CS,DE,'
While len(@my_var)>0
Begin
set @srt= substring(@my_var,0,charindex(',',@my_var)+1)
select @srt +'-->'+ cast( (LEN(@my_var) - LEN(REPLACE(@my_var, @srt, ''))) /LEN(@srt) as varchar(20))
set @my_var = REPLACE(@my_var, @srt, '')
End
Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008 |
|
|
|
praniktha
Starting Member
3 Posts |
Posted - 09/21/2012 : 00:05:11
|
| Thanx foryour reply.When i assign all the records to varchar(max) it exceeds the limit.huge amount of data in DB with various combinations.can u tell any other way.i want to get the precentage of each value |
Edited by - praniktha on 09/21/2012 00:06:16 |
|
|
|
senthil_nagore
Aged Yak Warrior
India
997 Posts |
Posted - 09/21/2012 : 00:26:30
|
I know its not an optimized way. But your table is not normalized, so you can try with temp table/table varaible, or you can go by row by row.
Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008 |
Edited by - senthil_nagore on 09/21/2012 00:44:46 |
|
|
| |
Topic |
|
|
|
Get the count of each value ina column
