I have this query:
SELECT companys.name as pav, SUM(distinct bills.amout) - SUM(cheques.amount)AS
amount FROM companys INNER JOIN bills ON companys.id = bills.company LEFT JOIN
cheques ON bills.bill_nr = cheques.bill_nr GROUP BY companys.name
For example if I have 2 bills with amounts (1000 and 1000) and 2 cheques for first bill (100 and 200) and for second 1 cheque (800), I get result -100 instead of 900. This happens because it sums bill amount 1000 only one time because they are same, but I need to sum them of distinct bills.bill.nr. How to do that? Thank you!
Your problem is that the checks are multiplying the billed amounts. A good solution is to aggregate everything before the join, so the numbers are all correct:
SELECT c.name as pav, (bamount - COALESCE(chamount, 0)) AS amount
FROM companys c INNER JOIN
(SELECT b.company, SUM(b.amount) as bamount
FROM bills b
GROUP BY b.company
) b
ON c.id = b.company LEFT JOIN
(SELECT b.company, SUM(ch.amount) as chamount
FROM bills b JOIN
cheques ch
ON b.bill_nr = ch.bill_nr
GROUP BY b.company
) ch
ON c.id = ch.company;
Notice that I added a COALESCE() so missing checks will not result in NULL values.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다