How to output total number of professions in SQL based on 4 tables

I have the following tables available through this link: tables. I’m trying to do a query where I list out the degrees (profession) and the counts associated with that profession. If the degree is listed more than twice, it should output ‘Popular Degree’. I coded:

select profession, count(profession_id), count(profession_id) > 2 as Popular
from Profession
where profession_id in
(select profession_id from provider_profession)
group by profession

I get an output of this, which is wrong: My query result

I am not sure how to query the result.

Answer

Try this:

select p.profession, 
count(pp.provider_id), 
case 
   when count(pp.provider_id)>2 then 'Popular degree'
   else ''
end as DegreePopularity
from profession p inner join provider_profession pp on p.profession_id=pp.profession_id
group by p.profession

I have tried to keep the answer as close to ANSI sql as possible, though only tested it out on a sql server