15,670,482 members
See more:
employeeid level
----------- -------
101 1,2,3
102 2,3
103 1,3,5
...
...

total my levels 1,2,3,4,5

now i want count no.of 1's in level column..
count no.of 2's in level column
Posted

## Solution 2

try this..

--My table
SQL
```Create table empotest
(Employeeid int,
Level varchar(100))

--insert
insert into empotest values (101,'1,2,3')
insert into empotest values (102,'1,2,3')
insert into empotest values (103,'4,5,6')

--My  sql query

drop table #temptest

DECLARE @Count INT
Declare @totalcount int
Declare @recordcount int
declare @string varchar(100)
declare @EmpId int
SET @Count = 0
set @recordcount=1
create table #temptest
(Empid int,
Level int)
select @totalcount=count(*) from empotest where Level is not null
select @totalcount
While @recordcount<=@totalcount
Begin
SET @Count = 0;
With CTE_test as
(select employeeid,level,Row_Number() over (order by EmployeeId asc) as rn from  empotest)
select @String=level,@EmpId=employeeid from cte_test where rn=@recordcount

WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
INSERT into #temptest values (@EmpId,SUBSTRING(@String,@Count,1))

END
SET @Count = @Count + 1
END
SET @recordcount = @recordcount + 1

END
select Level,count(Empid) from #temptest group by level
drop table #temptest```

Table:
101 1,2,3
102 2,3
105 4,5,6

Output

1 1
2 2
3 2
4 1
5 1
6 1

v2

## Solution 1

SQL
```select count(*) from table
where level like('%1%')```

or something like that should do the trick

v2

## Solution 3

Try

SQL
```SELECT COUNT(*) AS [Count], '1' AS [Level] FROM [YourTableName] WHERE Level Like '%1%'
UNION
SELECT COUNT(*) AS [Count], '2' AS [Level] FROM [YourTableName] WHERE Level Like '%2%'
UNION
SELECT COUNT(*) AS [Count], '3' AS [Level] FROM [YourTableName] WHERE Level Like '%3%'
UNION
SELECT COUNT(*) AS [Count], '4' AS [Level] FROM [YourTableName] WHERE Level Like '%4%'
UNION
SELECT COUNT(*) AS [Count], '5' AS [Level] FROM [YourTableName] WHERE Level Like '%5%'
ORDER BY [Level]```

Santhosh Kumar Jayaraman 2-Aug-12 6:17am
This wont work if level is like 11,12
__TR__ 2-Aug-12 6:21am
Yes. I agree with you. I wrote this assuming there will be only 5 levels (1 to 5).