Geek asks and answers

SQL query max (), count ()

database schema looks like

employee (employee_name, street, city)
work (employee_name, company_name, salary)
The company (company_name, city)
manages (employee_name, manager_name)

it is required to fulfill the request: to
find the company with the most employees.

I could find out the maximum score by request:

SELECT max( cnt ) max_cnt
FROM (

SELECT count( employee_name ) cnt, company_name
FROM works
GROUP BY company_name
)w1;

But now I canโ€™t find out the name of the company. If anyone has any idea, please share.

+2
source share
6 answers

To get the whole row containing the maximum value, you can use ORDER BY ... DESC LIMIT 1instead MAX:

SELECT company_name, cnt
FROM (
    SELECT company_name, count(employee_name) AS cnt
    FROM works
    GROUP BY company_name
) w1
ORDER BY cnt DESC
LIMIT 1
+4
source

How about something like:

SELECT count( employee_name ) cnt, company_name
FROM works
GROUP BY company_name
ORDER BY cnt DESC
LIMIT 1;

Edit:

MySQL

+2
SELECT company_name,count(*) as cnt 
FROM works 
GROUP BY company_name 
ORDER BY cnt DESC
+2
select company_name 
from works
group by company_name
having count(distinct employee_name)>=all(select count(distinct employee_name)
from works
group by company_name )
+1

Select * from(SELECT count(EmpName)cnt, CName FROM works GROUP BY CName Order By cnt desc) where ROWNUM = 1;
0

It looks like a course question.

If more than one company has the same largest number of employees, the query with LIMIT does not work. "ORDER BY" did not filter out useless information. Thus, we have the following solution

SELECT company_name FROM
(SELECT company_name, count(employee_name) cnt
    FROM works
    GROUP BY company_name) 
JOIN 
(SELECT max(cnt) max_cnt
FROM (
    SELECT count(employee_name) cnt
    FROM works
    GROUP BY company_name
)) ON cnt = max_cnt
0
source

More articles:


All Articles