Get the count of each value ina column

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

Get the count of each value ina column

Author

Topic

praniktha
Starting Member

3 Posts

Posted - 2012-09-20 : 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
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-09-20 : 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 - 2012-09-20 : 06:17:12

yes i want count of other values also ,the values may be of different combination & diff values

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-09-20 : 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 - 2012-09-21 : 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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-09-21 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources