SQL Sum function returning wrong quantity with inner join can't get subquery working

Question

the following code is outputting the wrong figure, I want a sum the total units invoiced on every invoice, where invoice date is between '2022-11-01' and '2022-11-30' Date is found on SalesInvoice table, the quantity is found in the SalesInvoiceLine table They are joined on SalesInvoiceId

This is the original code I wrote which outputs the wrong quantity:

select sum(SIL.QuantityInvoice) as QtyInv, 
from dr.SalesInvoiceLine SIL 
inner join dr.SalesInvoice as SI 
  on SI.SalesInvoiceId = SIL.SalesInvoiceId
where SI.TransactionDate between '2022-11-01' and '2022-11-30'

I have tried introducing a subquery but cannot get it working as requires group by and I'm not doing that correctly:

select SalesInvoiceId, 
  sum(QuantityInvoice) QtyInv 
from dr.SalesInvoiceLine SIL 
join (
  select SI.SalesInvoiceId 
  from dr.SalesInvoice SI 
  inner join dr.SalesInvoiceLine SIL2
    on SIL2.SalesInvoiceId = SI.SalesInvoiceId 
  where SI.TransactionDate between '2022-12-01' and '2022-12-31'
  ) 

I've looked at a number of resources and tried several alternatives but can't get the subquery version working.

Is a subquery the correct way to solve this and if so, how do I do it?

Answer 1

It appears you just need to group by each invoice using SalesInvoice.SalesInvoiceId.

 select sum(SIL.QuantityInvoice) as QtyInv, 
 from dr.SalesInvoiceLine SIL 
 inner join dr.SalesInvoice as SI 
  on SI.SalesInvoiceId = SIL.SalesInvoiceId
 where SI.TransactionDate between '2022-11-01' and '2022-11-30'
 group by SI.SalesInvoiceId