Yusha Yusha - 1 month ago 6
MySQL Question

How to count the number of occurrences of a specific value in a column in SQL without doing two SQL calls

I have a table: Judges(JName char(25), LawSchool char(25)).

I'm trying to retrieve the number of judges that attended Harvard and the number of Judges that attended Yale in one SQL Call. I know that I could do this

SQL CALL 1)

select LawSchool, count(*) as cnt from Judges where LawSchool = 'Harvard'


SQL CALL 2)

select LawSchool, count(*) as cnt from Judges where LawSchool = 'Yale'


But is there not a way I can retrieve the number of Judges who attended Yale and the number of judges who attended Harvard in one SQL call but store them in two variables such as cnt and cnt2?

Answer
select LawSchool, count(*) as cnt 
 from Judges 
 where LawSchool in ('Harvard','Yale')
 Group By LawSchool