CODEWITHSUNDEEP
sqlsql-server
Nilesh Gajare
Nilesh Gajare

Reputation: 6398

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

Upvotes: 1

Views: 1152

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

select
       category, count(*) Total, sum(oa.n) Processed, count(*) - sum(oa.n) Not_Processed
from table1
outer apply (
    select max(1) from table2 
    where table2.empid = table1.empid
    and table2.month = 'March'
    -- + other conditions here if needed
    ) oa (n)
group by 
       category

Results:

| category | Total | Processed | Not_Processed |
|----------|-------|-----------|---------------|
|        1 |     2 |         2 |             0 |
|        2 |     2 |         1 |             1 |

SQL Fiddle

CREATE TABLE Table1
    ([EmpID] int, [Code] varchar(5), [Name] varchar(3), [Category] int)
;

INSERT INTO Table1
    ([EmpID], [Code], [Name], [Category])
VALUES
    (1, '1008M', 'ABC', 1),
    (2, '1039E', 'XYZ', 1),
    (3, '1040E', 'TYS', 2),
    (4, '1041E', 'TYS', 2)
;


CREATE TABLE Table2
    ([EmpID] int, [Month] varchar(5), [Net Pay] int)
;

INSERT INTO Table2
    ([EmpID], [Month], [Net Pay])
VALUES
    (1, 'March', 1000),
    (2, 'March', 3000),
    (4, 'March', 3000)
;

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

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

Upvotes: 2

Related Questions