CODEWITHSUNDEEP
sqlcount
backtrack
backtrack

Reputation: 8154

group by in sql to find max count

Update I have a query like this 

select sl.College_ID,sl.Department_ID,COUNT(sl.RegisterNumber)  from StudentList sl
group by  sl.College_ID,sl.Department_ID
order by sl.College_ID,sl.Department_ID asc

abouve query gives the below result and i have 200 - college id and each college have 6 department_id i could get the count [No.of student ] in each department 

College_Id  Dept_Id  count 
1           1           100
1           2           210
2           3           120
2           6           80
3           1           340

but my question is i need to display the maximum count[student] for each department

some thing like this 

college_ID  Dept_Id  count 
3               1       340
26              2       250

and i tried this out but getting error 

select sl.College_ID,sl.Department_ID,COUNT(sl.RegisterNumber)  from StudentList sl
group by  sl.College_ID,sl.Department_ID
having  COUNT(sl.RegisterNumber)=max(COUNT(sl.RegisterNumber))
order by sl.College_ID,sl.Department_ID asc

what went wrong can some one help me

Upvotes: 0

Views: 2838

Answers (2)

Hardy
Hardy

Reputation: 1539

The result you want, which is group on college_ID and you not really care about college_ID, since from you example, with Dept_Id=1 will can not sure which college_ID is.

In that case, you can remove college_ID from your select statement and do a SUM GROUP BY

base on your query, something like:

SELECT t.Department_ID, SUM(t.c)
FROM (
select sl.College_ID,sl.Department_ID,COUNT(sl.RegisterNumber) c from StudentList sl
group by  sl.College_ID,sl.Department_ID
) t
GROUP BY t.Department_ID
ORDER BY SUM(t.c)

Note: If you really want college_ID in your result, you can do a JOIN to get your college_ID

Upvotes: 0

Bill Hall
Bill Hall

Reputation: 586

Maybe something like this?

SELECT sl.College_ID, sl.Department_ID, COUNT(sl.RegisterNumber) As StudentCount, s2.MaxCount
    FROM StudentList sl
    INNER JOIN (
        SELECT Department_ID, MAX(StudentCount) AS MaxCount
            FROM (
                SELECT College_ID, Department_ID, COUNT(*) As StudentCount
                    FROM StudentList
                    GROUP BY College_ID, Department_ID
                ) s1
            GROUP BY Department_ID
        ) s2 ON sl.Department_ID = s2.Department_ID
    GROUP BY sl.College_ID, sl.Department_ID, s2.MaxCount
    HAVING COUNT(sl.RegisterNumber) = s2.MaxCount
    ORDER BY sl.College_ID, sl.Department_ID ASC

EDIT: I've updated the query to more accurately answer your question, I missed the part where you want the College_ID with the max count.

EDIT 2: Okay, this should work now, I needed a second nested subquery for aggregating the aggregates. I don't know of a better way to compare the aggregates of different groups.

Upvotes: 2

Related Questions