declare @asd varchar(20)
set @asd ='asdaaaadffa'
select len(@asd) - len(replace(@asd,'a',''))

SELECT LEN(@asd) - LEN(REPLACE(@asd,'a',''))

DECLARE @asd VARCHAR(20)
SET @asd = 'asdaaaadffa'

SELECT 
	Letter, 
	Occurrences = COUNT(*)
FROM (
	SELECT Letter = SUBSTRING(@asd,n,1)
	FROM (SELECT TOP(LEN(@asd)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
		FROM sys.all_columns) tally
) d
GROUP BY Letter
ORDER BY Occurrences DESC