# count how many donations per test batch
select tb.id as testBatch_id, sum(case when d.id is null then 0 else 1 end) as donation_count
from TestBatch tb, DonationBatch db left join Donation d on d.donationBatch_id=db.id
where db.testBatch_id=tb.id
group by tb.id;

# find test batches with no test results
select tb.id as testBatch_id, sum(case when btr.id is null then 0 else 1 end) as test_count
from TestBatch tb, DonationBatch db left join Donation d on d.donationBatch_id=db.id left join BloodTestResult btr on btr.donation_id=d.id
where db.testBatch_id=tb.id
group by tb.id;