SQL Server 2008: Select name of column with greatest recurring value

I am trying to select the name of a field which occurs the most often in a table and in which a certain value is true.

Select
      Max(Count(Name))
From
      开发者_如何学运维EmployeeTreats
Where
      Donut = "Yes"
      And
      AmountEaten >= 10

Error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

What I am looking for is obviously something like: Edward has eaten the most with a sum total of 45

Name 
Edward

According to your initial question:

select top (1)
      [Name]
    , count(1) as Cnt
from Employees
where Donut = 'yes'
  and AmountEaten >= 10
group by [Name]
order by Cnt desc;

After your edit:

select top (1)
      [Name]
    , sum(AmountEaten) as TotalEaten
from Employees
where Donut = 'yes'
group by [Name]
order by TotalEaten desc;

This will handle the case where there's more than one Name with the same count.

select Name, count(*)
from ExployeeTreats
where  Donut = "Yes" and AmountEaten >= 10
group by Name
having count(*) >= ALL ( select count(*)
                         from EmployeeTreats
                         where Donut = "Yes" and AmountEaten >= 30
                         group by Name )

The trick is to group by name. Then order by count(*) descending, and clip off the top 1.

select top 1 max(name)
  from employeeTreats
 where donut='Yes'
   and amountEaten >= 10
 group by name
 order by count(*) desc