Select Record where Maximum Count of character e.g 'A'

Question

I'm trying to Select a Record that has the Maximum Count of a character e.g 'A' and get this error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery.". What is causing this, and how can I correct it.Thanks!

select 
*

from GROUPS as G
inner join STUDENT_GROUP as SG
on sg.Group_ID = g.Groups_ID
inner join STUDENT as S
on s.Student_ID = sg.Student_ID
inner join STUDENT_MARKS as SM
on SM.Student_ID=s.Student_ID
inner join smark as m
on m.mark_id=sm.Mark_ID

where m.Mark_name = max(count('A'));

in witch column (field) do you want to focus character counting? — Horaciux
Mr. Phronesis, I suggest you will make you question more precise. It would be excellent if you: a) create sqlfiddle for question, b) delete non-essential tables from request. A little hints: a) GROUP BY means HAVING (not WHERE), b) m.Mark_name - i suppose it's a varchar, not an integer, c) "has Maximum count" - I suppose you need calculation of counts, ORDER BY, TOP 1 — Alex Yu

Horaciux Horaciux · Accepted Answer · 2016-12-22T00:26:09

there is no such function to count the times a particular character appears in a string, but you can use the length of original string and the lengh of the string after removing this particular character as this:

declare @foo nvarchar(max)
set @foo='abcabc'

select len(@foo)-len(replace(@foo,'a','')) as [Total of 'a']

In your case, using TOP 1 will bring the first row in a particular order, and this order is count of character A

select top 1 *
from GROUPS as G
inner join STUDENT_GROUP as SG
on sg.Group_ID = g.Groups_ID
inner join STUDENT as S
on s.Student_ID = sg.Student_ID
inner join STUDENT_MARKS as SM
on SM.Student_ID=s.Student_ID
inner join smark as m
on m.mark_id=sm.Mark_ID
order by 
len(m.Mark_name)-len(replace(m.Mark_name,'A','')) DESC

As @jyao note, In case you need to get all records with the same max amount of character use TOP 1 WITH TIES as this:

select top 1 with ties *
...

Select Record where Maximum Count of character e.g 'A'

1 Answers