CODEWITHSUNDEEP
sqlsql-server
BumbleBee
BumbleBee

Reputation: 10779

Get the group which has maximum number of records in SQL Server

[ID] [Name]   [Dept] 
--------------------
1     Manu      A   
2     Anu       A   
3     Tanu      A   
4     Danu      A   
5     Anu       B   
6     Danu      B   
7     Danu      C   
8     Anu       C   
9     Tanu      C   
10    John      C   
11    Anu       D   
12    Jane      D   
13    Danu      D

I need to get the Dept with maximum number of employees.

Here is what I tried 

SELECT
    ID, Name, Dept
FROM
    (SELECT 
         *,
         rn = ROW_NUMBER() OVER(PARTITION BY Dept)
     FROM Emp) t
WHERE 
    rn = (SELECT MAX(rn) FROM t)

I need help in the WHERE clause.

Upvotes: 0

Views: 46

Answers (5)

Leif Neland
Leif Neland

Reputation: 1508

Ok, now you added the table structure:

;WITH c 
     AS (SELECT Dept, 
             Count(*) cnt 
         FROM   Emp 
         GROUP  BY Dept) 
SELECT c.* 
FROM   c 
WHERE  c.cnt = (SELECT Max(cnt) 
            FROM   c)

Upvotes: 1

LCIII
LCIII

Reputation: 3626

No WHERE is needed. Try using a GROUP BY

SELECT COUNT(Name) as NameCount, Dept from Table
GROUP BY Dept
ORDER BY COUNT(Name) DESC

The biggest group(s) will be at the top

Results

NameCount | Dept
    4        A
    4        C
    3        D
    2        B

Upvotes: 1

Leif Neland
Leif Neland

Reputation: 1508

Selecting all departments having the same max number of employees:

;WITH c -- create a list of depts and number of emp's 
     AS (SELECT deptid, 
            Count(*) cnt 
         FROM   emp 
         GROUP  BY deptid) 
SELECT d.* 
FROM   dept d 
   INNER JOIN c 
           ON d.deptid = c.deptid  
WHERE  c.cnt = (SELECT Max(cnt) 
            FROM   c)

Upvotes: 1

Leif Neland
Leif Neland

Reputation: 1508

I can't quite figure your table structure, but this selects the department with the most employees 

SELECT * from Dept WHERE deptid = (
  SELECT TOP 1 deptid FROM employees
  GROUP BY deptid
  ORDER BY COUNT(*) DESC

)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269723

You need aggregation to count the number of employees. The approach using row_number() is one approach, but with the right query:

SELECT Dept
FROM (SELECT Dept, COUNT(*) as cnt,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM Emp
     ) e
WHERE seqnum = 1;

However, a more common approach would just use ORDER BY and TOP:

SELECT TOP (1) Dept, COUNT(*) as cnt
FROM emp
GROUP BY Dept
ORDER BY COUNT(*) DESC;

If you wanted ties, then you would use WITH TIES in the SELECT.

Upvotes: 2

Related Questions