Ch1   Ch2   Ch3   Ch4     NoOf3s   NoOf7s
---   ---   ---   ---     ------   ------
1     3     4     3       2        0
7     7     7     3       1        3
7     8     9     0       0        1

create table #ch
(
	ch1	int,
	ch2	int,
	ch3	int,
	ch4	int
)

insert into #ch
select 1,     3,     4,     3	union all
select 7,     7,     7,     3   union all
select 7,     8,     9,     0 

create function dbo.count_no
(
	@ch1	int,
	@ch2	int,
	@ch3	int,
	@ch4	int,
	@cnt_no	int
)
returns int
as
begin
	declare @count	int

	select @count = count(*)
	from	
	(
		select	@ch1 as ch	union all
		select	@ch2	union all
		select	@ch3	union all
		select	@ch4
	) as c
	where	ch = @cnt_no
	return @count
end


select 	ch1, ch2, ch3, ch4, 
	dbo.count_no(ch1, ch2, ch3, ch4, 3) as NoOf3s,
	dbo.count_no(ch1, ch2, ch3, ch4, 7) as NoOf7s
from	#ch

select
	ch1,
	ch2,
	ch3,
	ch4,
	NoOf3s =
		case ch1 when 3 then 1 else 0 end +
		case ch2 when 3 then 1 else 0 end +
		case ch3 when 3 then 1 else 0 end +
		case ch4 when 3 then 1 else 0 end,
	NoOf7s = 
		case ch1 when 7 then 1 else 0 end +
		case ch2 when 7 then 1 else 0 end +
		case ch3 when 7 then 1 else 0 end +
		case ch4 when 7 then 1 else 0 end
from
	MyTable