How to SELECT Distinct Count from SELECT with Union All SELECT-CodePudding

I need to get a count from a query with UNION ALL, but all my attempts so far have failed and I cannot get it to work.

My Initial SELECT (Which returns 8 unique departments)

select distinct Department 
from ( select Department
       from EFP_EmployeeFollowupManagerCommit
       union all
       select Department from EFP_EmploymentUser ) 
a order by Department;

I have tried different variations of the following

SELECT COUNT(Department) as "DepCount"
FROM ( select Department
       from EFP_EmployeeFollowupManagerCommit
       union all
       select Department
       from EFP_EmploymentUser );

Can anyone help?

CodePudding user response：

You have to write like this: select count(distinct Department)

CodePudding user response：

You can try the following query, it should work:

select count(*) from (select distinct Department 
    from ( select Department from EFP_EmployeeFollowupManagerCommit 
    union all 
    select Department from EFP_EmploymentUser ));