Counting totals on two separate SQL tables

I have two (identical for this purpose) tables

ToDoList

ItemBatch | Item
   abc       Pickup sticks
   abc       Tie my shoe
   xyz       Suck my thumb
   abc       Climb a tree

DoneList

ItemBatch | Item
   abc      Tie my shoe

I’m trying to get a count from each of these per [ItemBatch] but not sure if that’s actually possible in one query?

Separately I’d just do

select count(*) from ToDoList where ItemBatch = 'abc' /* repeat for each ItemBatch */

select count(*) from DoneList where ItemBatch = 'abc' /* repeat for each ItemBatch */

and assign to variables in the front-end so I could get the following:

ItemBatch | Total | DoneList | DoneList - ToDoList
   abc       3        1           2
   xyz       1        0           1

Is there a query that can give me that entire output in one fell swoop? I tried joining for starters

select count(cc.[ItemBatch]), count(cq.[ItemBatch])
from ToDoList cc
left join DoneList cq on cc.[ItemBatch] = cq.[ItemBatch]

Quickly realised that’s not going to work…

Then I thought about

select
(select count(*) from ToDoList where [ItemBatch] = 'abc' ) as Total,
(select count(*) from DoneList where [ItemBatch] = 'abc' ) as DoneList

but I can’t figure out how to get all rows outputted for each ItemBatch in one go, group by doesn’t work for that last statement:

select
    (select [ItemBatch], count([ItemBatch]) from ToDoList group by [ItemBatch) as Total,
    (select [ItemBatch], count([ItemBatch]) from DoneList group by [ItemBatch]) as DoneList

Answer

You should try to use OUTER APPLY in your case because of the calculation in the SELECT statement.

The OUTER APPLY operator returns all the rows from the left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression.

So, your statement should be:

SELECT 
        cc.ItemBatch, 
        COUNT(*) as Total,
        ISNULL(dl.DoneListTotal, 0) as DoneList,
        COUNT(*) - ISNULL(dl.DoneListTotal, 0) as [DoneList - ToDoList]
FROM ToDoList cc
     OUTER APPLY (SELECT COUNT(*) DoneListTotal
                  FROM DoneList cq
                  WHERE cc.ItemBatch = cq.ItemBatch) dl
GROUP BY cc.ItemBatch, dl.DoneListTotal;