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

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

CodePudding user response：

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;