Click here to Skip to main content
13,044,467 members (79,057 online)
Click here to Skip to main content
Add your own
alternative version


2 bookmarked
Posted 25 Jul 2012

Number of occurences of any record in a column

, 25 Jul 2012
Rate this:
Please Sign up or sign in to vote.
Getting the count of records in a particular column.


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 noNameFather NameAddressMerit MarksCollege id

And the College table will be look like:

College idCollege NameAddressReg 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)
return @college_name

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Durgesh Swarnkar
Software Developer (Junior) Forbes Technosys Ltd.
India India
This member doesn't quite have enough reputation to be able to display their biography and homepage.

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 25 Jul 2012
Article Copyright 2012 by Durgesh Swarnkar
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid