Q:

Using Subquery in group By or where clause SQL Server

I want to create summary report. 
Below are table structures
Table 1 : 
EmpID |  Code |  Name | Category
------+-------+-------+----------
1     | 1008M | ABC   | 1
2     | 1039E | XYZ   | 1
3     | 1040E | TYS   | 2
4     | 1041E | TYS   | 2

Table 2 :
EmpID |  Month     |  Net Pay
------+------------+----------
1     | March      | 1000     
2     | March      | 3000  
4     | March      | 3000  

Desired output should show how many employees are present in single category (TOTAL column) 
A Processed column will show the number of employees process in particular month for e.g. March which will come from Table 2
Not Processed will show the difference between Total and Processed column values
Category | Total |  Processed | Not Processed
---------+-------+------------+--------------
1        | 2     | 2          | 0
2        | 2     | 1          | 1

I tried using below query but it is not working. 
select 
    B.Category,
    processcount = (select count(*) from Table2 A 
                    where A.Companyid = 2 
                      and A.Month = 'March' 
                      and A.Employeeid = b.Id) 
from 
    Table1 B 
where 
    B.Companyid = 2 and empstatus = 1 
group by 
    Category

A:

Try this
with cte
as
(
select
t1.category,
Cnt = case when t2.empid is null then 0
          else 1 end
from table1 t1
left join table2 t2
on t1.empid = t2.empid and t2.Month='March'
)
select
Category,
Total = count(1),
Processed = sum(cnt),
NotProcessed=sum(case when cnt=0 then 1 else 0 end)
from cte
group by category

