Вложенная агрегатная функция в SQL Server

Просто используйте ORDER BY (для сортировки) и TOP (1), чтобы получить одну верхнюю строку:

SELECT TOP (1) COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

SELECT  COUNT (CustomerID),Country FROM Customers 
GROUP BY Country
HAVING COUNT (CustomerID) = (SELECT MAX (tt) c FROM ((SELECT count (CustomerID) tt FROM Customers GROUP BY Country)) AS a)

Решение Marc_s было моей первой мыслью +1

Однако, если ваш CustomerID не уникален и/или вы хотите увидеть совпадения (крайний случай)

Пример

Declare @YourTable Table ([Country] varchar(50),CustomerID varchar(50))  Insert Into @YourTable Values 
 ('USA','AA')
,('USA','AB')
,('USA','AB')     -- USA has 3 CustomerIDs    (2 distinct)
,('France','AC')
,('France','AD')  -- France has 2 CustomerIDs (2 distinct)
,('Singapore','AE')


;with cte as ( 
Select country
      ,CustCnt = count(Distinct CustomerID)  -- Distinct may not be necessary if already unique 
 from @YourTable
 Group By Country
)
Select top 1 with ties *
 From  cte
 Order by dense_rank() over (order by CustCnt desc)

Полученные результаты

country     CustCnt
France      2
USA         2