SQL Server SQL using count and max functions at the same time

8e2ybdfx  于 2023-06-28  发布在  其他
关注(0)|答案(2)|浏览(129)

I have a query and it brings me how many people live in which country. Select Country, COUNT(EmployeeID) from Employees group by Country

What kind of query should I use if I want to get the most populated city and the number of people living in it?

The code I tried : Select Country, MAX(COUNT(EmployeeID)) from Employees group by Country

sbtkgmzw

sbtkgmzw1#

You can use TOP :

Select TOP 1 Country, COUNT(EmployeeID) as [count]
from Employees
group by Country
order by [count] desc
f4t66c6m

f4t66c6m2#

Try this. qty - count for country, maxqty - max count for all country.

Select Country
  ,COUNT(EmployeeID) qty
  ,MAX(COUNT(EmployeeID))over() maxqty
from Employees 
group by Country

For test data

create table Employees (EmployeeId int,country varchar(20));
insert into Employees values 
 (1,'country1'),(2,'country1')
,(3,'country2'),(4,'country2'),(5,'country2')
;

Result

Countryqtymaxqty
country123
country233

相关问题