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 SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue 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.