I have 3 tables to calculate payments, received and paid:

1. Company

2. Pay:

3.Receive:

I need to make a report with amount paid and received during a certain period, but somehow its duplicating sum and I getting this result:

I tried this query:

SELECT o.company, 
       SUM(n.valuep) AS value_pay, 
       SUM(r.valuer) AS value_receive 
FROM app_company AS o 
LEFT JOIN app_pay AS n ON o.idcompany=n.company AND (date(n.duedate) BETWEEN '2022-10-01' AND '2022-10-30') 
LEFT JOIN app_receive AS r ON o.idcompany=r.company AND (date(r.issuedate) BETWEEN '2022-10-01' AND '2022-10-30') 
GROUP BY o.company

I tried LEFT JOIN to get all results even if it is empty in one of columns, I tried to pre-aggregate, but I could not get my query to work. I am missing something.

CodePudding user response：

You need to do the aggregation on the subqueries and then join .

SELECT o.company, 
       value_pay, 
       value_receive 
FROM app_company AS o 
LEFT JOIN  (select sum(valuep) as value_pay,
                   company
            from app_pay
            where date(dueday) BETWEEN '2022-10-01' AND '2022-10-30'
            group by company
             ) AS n ON o.company=n.company 
LEFT JOIN  (select sum(valuer)  AS value_receive ,
                   company
            from app_receive
            where date(issuedate) BETWEEN '2022-10-01' AND '2022-10-30' 
            group by company
            )  AS r ON o.company=r.company 
order by o.company asc   ;

https://dbfiddle.uk/0mVKI_tF