Why does my SQL Statement not SUM() up correctly?

I have the following statement:

SELECT 
   ROUND(SUM(invoicetitle.unitpricegross*invoicetitle.suppliedquantity),2) as Costs,
   SUM(invoicetitle.suppliedquantity) AS Unitamounts 
FROM invoicetitle 
WHERE 
  ((SELECT invoice.state 
    FROM invoice where invoicetitle.invoiceid = invoice.invoiceid 
     and (invoice.invoicedate >= 1609459200000 and invoice.invoicedate <= 1640908800000)) = (1 or 4)) 
GROUP BY invoicetitle.invoicetitle_number

note that = (1 or 4) refers to two statements in the database where 1 is sold and 4 is a refund.

With = (1)) I get the following results:

Costs – Unitamounts 3.281,10 – 582

With = (4)) I get the following results:

Costs – Unitamounts -115,2 – -32

With = (1 or 4)) I get the following results:

Costs – Unitamounts 3.281,10 – 582

But I expect as a correct SUM() of it:

Costs – Unitamounts 3.165,9 – 550

What am I doing wrong that the results are not subtracted correctly?

Answer

You probably meant to do:

SELECT 
   ROUND(SUM(invoicetitle.unitpricegross*invoicetitle.suppliedquantity),2) as Costs,
   SUM(invoicetitle.suppliedquantity) AS Unitamounts 
FROM invoicetitle 
INNER JOIN invoice ON invoicetitle.invoiceid = invoice.invoiceid 
          and (invoice.invoicedate BETWEEN 1609459200000 
                                      and  1640908800000)
WHERE invoice.state IN (1,4)
GROUP BY invoicetitle.invoicetitle_number