I'm trying to construct a query that combines two unmatched data types.
Here's the schema I'm working with:
Skill (skill_id int, skill text)
Foreign Key: None
Skillrel (skill_id, agent id)
Foreign Key: agent_id->agent(agent_id), skill_id->skill(skill_id)
Agent (agent_id int, first text, middle text, last text, address text, city text, country text, salary int, clearance_id int)
Foreign Key: clearance_id->securityclearance(sc_id)
and this is the query I have:
select skill from skill where skill_id in (select skill_id from skillrel group by skill_id order by count(*) desc limit 1);
This returns (I think) the skill most common among all agents. That part works fine. The problem is that I also need the query to return the count from the subquery. I can get that with this:
select count(*) from skillrel group by skill_id order by count(*) desc limit 1;
but I can't figure out the syntax to join the two into a single result. THe core issue I'm running into seems to be that the first query returns a text string and the 2nd a bigint, and these can't be combined.
This is a homework assignment, so I'm more expecting hints than actual answers, but any info is appreciated!
Original prompt:
Find the skill that is most common among all agents, and the number of agents having that skill.