Nested Aggregate function in SQL Server-CodePudding

I have a table Customers which contains customerID and Country columns. I would like to return the country with the most customers and the count of the customers.

This is what I tried:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) = MAX(COUNT(CustomerID));

How would I correctly implement the code? Thank you in advance.

CodePudding user response：

Just use ORDER BY (to sort) and TOP (1) to get the single top row:

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

CodePudding user response：

Marc_s's solution was my first thought 1

However, if your CustomerID is not unique and/or you want to see ties (edge case)

Example

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)

Results

country     CustCnt
France      2
USA         2

CodePudding user response：

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)