SUM return wrong value when join 3 table - MS SQL

Question

I have 3 table need to join namely table invoice, items and payments. The table items and payments will join to the table Invoice based on invoice id

Now I need to get SUM of sales, sales after cost and payment by invoice date.

So this is my query

SELECT SUM((I.price * qty) - (I.price * qty * discount)) as totalSales,SUM((I.price * qty) - (I.price * qty * discount)-costItem) as salesAfterCost, SUM(PAY.amount) as paymentReceived
FROM tbl_inv B LEFT JOIN tbl_item I ON B.id = I.id_invoice 
LEFT JOIN tbl_payment PAY ON B.id = PAY.id_invoice
WHERE B.dateInv = '2013-04-01'
GROUP BY B.id,b.dateInv;

And the result will return like this

totalSales  salesAfterCost  paymentReceived
 75.540000    67.540000        622.8000

But when I check with this query to get total payment, it will return with different value.

SELECT SUM(PAY.amount) paymentReceived
FROM tbl_inv B LEFT JOIN tbl_payment PAY ON B.id = PAY.id_invoice
WHERE B.dateInv = '2013-04-01';

Result :

paymentReceived
155.7000

And query for sales

SELECT SUM((I.price * qty) - (I.price * qty * discount)) as totalSales,SUM((I.price * qty) - (I.price * qty * discount)-costItem) as salesAfterCost
FROM tbl_inv B LEFT JOIN tbl_item I ON B.id = I.id_invoice
WHERE B.dateInv = '2013-04-01';

Result :

totalSales  salesAfterCost
37.770000   33.770000

How can I resolved this thing?

@Damien_The_Unbeliever is right. But it could also be resolved if you can join a payment to a specific item on an invoice. — davmos
– davmos, Commented May 1, 2013 at 7:17
@davmos - given that payments are being multiplied by 4, and sales by 2, I don't think that there's a 1-1 correspondence between invoices and payments. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 1, 2013 at 7:20
Sure @Damien_The_Unbeliever, but I didn't say that. I just hinted at the possibility of a 1-1 relationship between an item on an invoice and a payment. Probably not possible, but thought I'd throw it into the mix to aid understanding. — davmos
– davmos, Commented May 1, 2013 at 7:28

Damien_The_Unbeliever · Accepted Answer · 2013-05-01 07:10:21Z

4

Move the SUM()s into subqueries - at the moment, every row from payment is being matched against every row from invoice, resulting in a cartesian join

SELECT I.totalSales,I.salesAfterCost, PAY.paymentReceived
FROM tbl_inv B
LEFT JOIN (
   select id_invoice,SUM((I.price * qty) - (I.price * qty * discount)) as totalSales,
       SUM((I.price * qty) - (I.price * qty * discount)-costItem) as salesAfterCost
  from tbl_item group by id_invoice) I
      ON
         B.id = I.id_invoice 
LEFT JOIN (
    select id_invoice,SUM(amount) as paymentReceived
    from tbl_payment group by id_invoice) PAY
       ON
         B.id = PAY.id_invoice
WHERE B.dateInv = '2013-04-01'

answered May 1, 2013 at 7:10

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

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

Collectives™ on Stack Overflow

SUM return wrong value when join 3 table - MS SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related