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 and 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

1

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;

edited Apr 11 '22 at 14:44

answered Apr 11 '22 at 14:33

forpas

145,388
9
31
69

the answer is perfect , everything works except that sum(amount) from invoice and sum(amount) of sale column is incorrect value – Abey Apr 11 '22 at 17:27
also why are we left joining only no ON c.customer_id = i.customer_id and also not on ON c.customer_id = s.customer_id ? – Abey Apr 11 '22 at 17:27
@Abey both subqueries group by customerid and return the sum of amounts for each customer for the dates <='2022-04-30'. Isn't this that you want? I join ON c.customer_id = s.customer_id the 1st subquery and ON c.customer_id = i.customer_id the 2nd subquery. If you expect different results then provide sample data and expected results so I can check. – forpas Apr 11 '22 at 17:32

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

1 Answers1