Click here to Skip to main content
Click here to Skip to main content

Tagged as

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.

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:

Roll noNameFather NameAddressMerit MarksCollege id
1ASABC6701
2BPFGH7801
3CRLMN4501
4DTXYZ8902

And the College table will be look like:

College idCollege NameAddressReg no.
01D.I.C.T.XYZ0997
02IIIMPQR0776
03TITIABC1209

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.

License

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

Share

About the Author

Durgesh Swarnkar
Software Developer (Junior) Forbes Technosys Ltd.
India India
I am Durgesh Swarnkar. Currently I am an Software Developer in Forbes Technosys, Mumbai. I have done PG with Computer Application. I have the knowledge of ASP.Net, C#, VB.Net and little bit of WPF. I have good command over SQL Server.
Follow on   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140814.1 | Last Updated 25 Jul 2012
Article Copyright 2012 by Durgesh Swarnkar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid