Number of occurences of any record in a column

Introduction

Sometimes we have to find the number of occurrences of any repetitive record in a particular column so we can use the COUNT function of SQL Server but in some cases we have to find the count as well as another column with another table using inner join. So in that case the situation become little more difficult. For this i am giving you a certain example which will help you to getting out of that situation.

Using the code

The problem is described below:

We have two tables named Student and College. Student table has all the information about the students as well as the college code in which they got admitted. the structure of the table will look like this:

And the College table will be look like:

Now by using these two tables we want to get the name of the college which has the maximum number of admission means the number of occurrences in the collegeid column of student is maximum.

A sample code is given that how the data is retrieved by using COUNT and the join.

Here a function is created to get the desired result.

Create function [dbo].[max_admission]()
returns
varchar(50)
begin
declare @college_name varchar(50)
set @college_name=(select TOP(1) COLLEGE_NAME from 
(select college_id ,count(*) allot from Stud group by college_id ) a, college_info c 
where a.college_id = c.College_id  ORDER BY allot DESC)
return @college_name
end

This sql UDF will bring the result of our interest. This makes the thing simpler and quicker.

Points of Interest

Firstly I used two queries to get the desired result. First query returns the maximum value of the occurrence and the other one brings the name of college with respect to that maximum number but after that summarizing both the queries and made the single query with appropriate result.