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:
|Roll no||Name||Father Name||Address||Merit Marks||College id|
And the College table will be look like:
|College id||College Name||Address||Reg no.|
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]()
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)
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.