Incorrect sum of columns on second table when using 2 inner joins

Question

I have 3 SQLite tables customer: sale and invoice. The sum that I get on invoice.amount is incorrect in the below query but the sum.amount on sale table is correct.

My query is:

SELECT  sum(invoice.amount)  as 'amt' 
       , customer.name
       , sum(sale.amount) as 'amt1' 
FROM    customer 
          INNER JOIN sale on customer.customer_id  = sale.customer_id 
          INNER JOIN invoice on customer.customer_id = invoice.customer_id
WHERE  (
         (sale.date <='2022-04-30') and 
         (invoice.date <='2022-04-30') and 
         customer.area='xyz' and 
         ( 
             customer.status='Active' OR 
             customer.status='Inactive'
         ) 
) 
GROUP BY customer.customer_id 
ORDER BY customer.delseq ASC

If I only use one inner join as shown below, and skip sale table, then I get correct results.

SELECT  sum(invoice.amount)  as 'amt' 
       , customer.name 
FROM   customer 
           INNER JOIN invoice ON customer.customer_id = invoice.customer_id
WHERE  (
          ( invoice.date <='2022-04-30') and 
            customer.area='xyz' and 
            ( 
               customer.status='Active' 
               OR 
               customer.status='Inactive'
            ) 
) 
GROUP BY customer.customer_id 
ORDER BY customer.delseq ASC

Please post a) some sample rows from all tables b) the expected results c) what do you mean by "incorrect"? — SOS, Apr 11 '22 at 12:39
Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, Apr 12 '22 at 00:03
@philipxy your submitted alternate answer is also perfect. its exactly what #forpas suggested below. If only i had asked this questions earlier, . i was going nuts over this the entire day :) — Abey, Apr 12 '22 at 04:18
You can click on something offered by my having clicked on this as a duplicate (which generated that comment from me) to say this is a duplicate (which will close it to further answers). PS For the future: Here are my standard comment re duplicates/searching & my standard comment re code/debug questions: — philipxy, Apr 12 '22 at 04:30
Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. If asking reflect research. See [ask], [Help] & the voting arrow mouseover texts. If you post a question, use one phrasing as title.) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) — philipxy, Apr 12 '22 at 04:32
Please in code questions give a [mre]--cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Apr 12 '22 at 04:33

forpas · Accepted Answer · 2022-04-11T14:44:53.400

You must aggregate separately in each table and then join and maybe use LEFT instead of INNER joins if there is a case that for a customer there no sales or invoices for the given date range:

SELECT c.name, i.amt, s.amt1
FROM customer c
LEFT JOIN (
  SELECT customer_id, SUM(amount) amt1
  FROM sale 
  WHERE date <='2022-04-30'
  GROUP BY customer_id
) s ON c.customer_id  = s.customer_id 
LEFT JOIN (
  SELECT customer_id, SUM(amount) amt 
  FROM invoice 
  WHERE date <='2022-04-30'
  GROUP BY customer_id
) i ON c.customer_id  = i.customer_id 
WHERE c.status='Active' OR c.status='Inactive'
ORDER BY c.delseq ASC;

Incorrect sum of columns on second table when using 2 inner joins

1 Answers1