How to Join Queries from One Table?

Question

First Query:

 select 
    Batch,Shiplist_Qty,COUNT(X.Batch) as ALLOCATED,(Shiplist_Qty - COUNT(X.Batch)) as Variance
     from dbo.FG_FILLIN as X
    where X.Status in('KITTED','ALLOCATED') group by X.Batch,X.Shiplist_Qty

Output:

 Shiplist_Qty   ALLOCATED   Batch   Variance
    2              2        CIP2    0
    8              6        CIP4    2

Second Query:

Select BAtch,
COUNT(X.Batch) as KITTED from dbo.FG_FILLIN as X
where X.Status='KITTED' group by X.Batch

OUtput:

Batch   KITTED
CIP2    1

Now I need to join this two query and show result something like this.

 Shiplist_Qty   ALLOCATED   Batch   Variance  Kitted
        2              2        CIP2    0           1
        8              6        CIP4    2           0

Thanks in Regards!

Answer 1

Try this:

select Batch, Shiplist_Qty,COUNT(X.Batch) as ALLOCATED, 
    (Shiplist_Qty - COUNT(X.Batch)) as Variance, ISNULL(Kitted, 0) as Kitted
from dbo.FG_FILLIN as X
LEFT OUTER JOIN
(
    Select Batch,
    COUNT(Y.Batch) as KITTED from dbo.FG_FILLIN as Y
    where Y.Status='KITTED' group by Y.Batch
) Batches
ON X.Batch = Batches.Batch
where X.Status in('KITTED','ALLOCATED') 
group by X.Batch,X.Shiplist_Qty, ISNULL(Kitted, 0)

Answer 2

You can use subquery:

select 
    Batch,
    Shiplist_Qty,
    COUNT(X.Batch) as ALLOCATED,
    (Shiplist_Qty - COUNT(X.Batch)) as Variance,
    (select Count(Batch) from dbo.FG_FILLIN where Status='KITTED' and Batch = X.Batch) as Kitted
from dbo.FG_FILLIN as X
where X.Status in('KITTED','ALLOCATED') group by X.Batch,X.Shiplist_Qty