CODEWITHSUNDEEP
sqloracle-databaseplsql
Walt
Walt

Reputation: 111

PLSQL select most occuring value

I want to do the following: Creating a procedure that returns the section_id value with the most amount of enrollments. This can be calculated by counting the amount of student_id's in that table.

Example:

SELECT count(student_id) amount
FROM enrollments
WHERE section_id=20;

This would return the amount of enrollments for section number 20.

But I want it to return the amount of the section with the most enrollments. But I don't know how, any suggestions on how to solve this? As I mentioned I use PLSQL so all those functions may be used here

Upvotes: 0

Views: 108

Answers (3)

Gauravsa
Gauravsa

Reputation: 6514

You can try this:

  SELECT section_id, count(student_id) as StudentCount
  FROM enrollments a
  group by section_id
  having count(student_id) = (SELECT max(count(student_id)) 
  FROM enrollments a
  group by section_id);

SQL Fiddle: http://sqlfiddle.com/#!4/24f7c/1

Here's an interesting link on how to use keep and solving the same query:

http://rwijk.blogspot.com/2012/09/keep-clause.html

Upvotes: 1

Matthias Wiedemann
Matthias Wiedemann

Reputation: 1599

You can try this:

SELECT count(student_id) amount FROM enrollments 
group by section_id
Order by amount desc
limit 1

Upvotes: 0

Jesse Gador
Jesse Gador

Reputation: 85

Please try this:

SELECT section_id, count(student_id) amount
FROM enrollments
GROUP BY section_id

Upvotes: 0

Related Questions