I assume that you want to group on the
ClassId fields, and that the
Sex field in the result is not sex at all, but the number of persons.
Something like this:
select NameId, ClassId, count(*) as Sex, sum(Sex) as Male, count(*) - sum(Sex) as Female from TheTable group by NameId, ClassId
For Sex values 1 and 2 you can use a
case to count them:
sum(case Sex when 1 then 1 else 0 end) as Male, sum(case Sex when 2 then 1 else 0 end) as Female