Pfthb

I have a table called employee like below:

emp dept
---- ----
1 HR
2 Accounts
3 HR
4 Dev
2 Dev

It is possible for an employee to belong to more than one department, for example in the case of employee 2.

I want an output like this :

dept empInDept totalCountofEmp
 ----- --------- ----------------
 HR 2 4
 Accounts 1 4

I can get a count of employees in specified departments like below:

select dept,count(*) as empInDept from employees where dept ='HR' or dept='Accounts' group by dept

But I am not sure if it is possible to create a single query where it is possible to get result of above select query and also get the overall distinct count of employees from this table.

Here an example of your request:

create table #temp
(id int, value int)

insert into #temp values (1,2),(2,3),(1,5)

select id, count(Value), (select count(distinct value) from #temp) as X
from #temp
group by id

For your specific task, please try the following:

select dept,
 count(*) as empInDept 
 (select count(distinct emp) from employees) as TotalCount
from employees where dept ='HR' or dept='Accounts' 
group by dept

You can use the ANSI/ISO standard rollup or grouping sets:

select dept, count(distinct emp)
from employees
where dept in ('HR', 'Accounts')
group by grouping sets (dept, ());

More databases support rollup, but the syntax can vary. One method is:

select dept, count(distinct emp)
from employees
where dept in ('HR', 'Accounts')
group by rollup(dept);