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

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?

CodePudding user response：

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