List institutes where more than half of the applicants accepted

[nian123]

I have 2 tables:

tbl1: InstituteID, name
tbl2 ApplicantID, InstituteID ,Applicant_Name, address, city, state, status *

* Status field contains value A for acceptance or R for rejected

I need to list Institute names where more than half of applicants were accepted.

 

[gmmastros]

try this:

; With Counts As
(
  Select   InstituteId
  From     tbl2
  Group By InstitudeId
  Having   1.0 * Count(Case When Status='A' Then 1 End) > 1.0 * Count(1) / 2
)
Select  tbl1.*
From    tbl1
        Inner Join Counts
          On tbl1.InstitudeId = Counts.InstituteId

If this works and you would like me to explain it, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

Makes me wonder, if a CTE has to be used at all.

--sample data
declare @tbl1 Table (InstituteID int, Name nvarchar(50))
declare @tbl2 Table (ApplicantID int, InstituteID int, status char(1))

insert into @tbl1 values (1,'Institute 1')
insert into @tbl1 values (2,'Institute 2')

insert into @tbl2 values (1,1,'A')
insert into @tbl2 values (2,1,'R')
insert into @tbl2 values (3,1,' ')

insert into @tbl2 values (4,2,'A')
insert into @tbl2 values (5,2,'R')
insert into @tbl2 values (6,2,'A')

--institute query
Select t1.name from @tbl2 t2
Inner Join @tbl1 t1 on t1.InstituteID = t2.InstituteID
Group By t1.InstituteID, t1.name
Having Count(Case When status='A' Then 1 End)>Count(*)/2

Bye, Olaf.

 

[gmmastros]

Olaf,

You're probably right that a CTE is not required. In this case, I suggested it because it could improve performance (depending on the data). If tbl2 has a lot of rows and only a small handful satisfy the requirements, then it is not necessary to join a large table to a small table. I mean... it could be that you join a small result set to a small table, which should improve performance.

As usual... it depends.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom