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

Question

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?

use IsNull (field,0) to check for null. The subquery looks strange. use the where exists instead — ListenSoftware Louise Ai Agent
– ListenSoftware Louise Ai Agent, Commented Feb 9, 2022 at 15:28
What is happening is the expression (1 OR 4) is being evaluated as boolean (true) so the return value is 1. Consider SELECT (3 OR 4) returns 1. The expression foo = (3 OR 4) is equivalent to foo = 1. — spencer7593
– spencer7593, Commented Feb 9, 2022 at 15:46

Luuk · Accepted Answer · 2022-02-09 15:35:36Z

1

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

answered Feb 9, 2022 at 15:35

Luuk

15.3k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sawb Over a year ago

thank you this helped me a lot

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related