select count(*) as number_of_empls_with_multiple_roles from ( select e.id , e.name from employees as e inner join employee_roles as er on e.id = er.empl_id group by e.id , e.name having count(*) > 1 ) as empls_with_multiple_roles
select count(*) as number_of_roles_with_multiple_empls from ( select r.id , r.name from roles as r inner join employee_roles as er on r.id = er.role_id group by r.id , r.name having count(*) > 1 ) as roles_with_multiple_empls
Run the subqueries by themselves to assure yourself that they return the correct data to be counted.
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.