sum of values of more than one table

Question

I have a problem in the code below. When I put to add the field valor table lnota, it brings a wrong result of the account; if I leave only the field, it brings the right value

SELECT projeto.Projeto, 
   COALESCE(SUM(recebimento.Valor), 0) AS Total, 
   COALESCE(SUM(contaspagar.Valor), 0) AS Total2, 
   COALESCE(SUM(lnota.Valor), 0) AS Tota3 
FROM projeto 
LEFT JOIN recebimento ON recebimento.Projeto = projeto.IdProjeto 
LEFT JOIN contaspagar ON contaspagar.Projeto = projeto.IdProjeto 
LEFT JOIN lnota ON lnota.Projeto = projeto.IdProjeto 
GROUP BY projeto.Projeto

Answer 1

The Problem

The problem lies in the form in which the left join is being employed, since the relationship 1 for many between the various united relationships leads to the repetition of values according to the correspondence of the keys of the query. As for 1 project there may be N receipts, we will have at the end of the left join all tuples of the project table plus all receiving tuples

Examples of solutions

1. Using a subconsultation for each table generating a spine of total

   SELECT projeto.idprojeto, projeto.projeto, 
   (select COALESCE(SUM(recebimento.valor), 0)  
      from recebimento 
      where recebimento.projeto = projeto.idprojeto) AS Total_Recebimento,
   (select COALESCE(SUM(contaspagar.valor), 0)  
      from contaspagar 
      where contaspagar.projeto = projeto.idprojeto) AS Total_contaspagar,
   (select COALESCE(SUM(lnota.Valor), 0)  
      from lnota 
      where lnota.projeto = projeto.idprojeto) AS Total_lnota
   FROM projeto 
   

2. Using a subconsultation to generate summarized relationships per project and join them through the left join

   SELECT p.*, 
          COALESCE(r.Total_Recebimento, 0) as Total_Recebimento, 
          COALESCE(cp.Total_contaspagar, 0) as Total_contaspagar,
          COALESCE(n.Total_lnota, 0) as Total_lnota
   FROM projeto p
   LEFT JOIN (select recebimento.projeto, SUM(recebimento.valor) AS Total_Recebimento 
                from recebimento 
                group by projeto) r on r.projeto = p.idprojeto 
   LEFT JOIN (select contaspagar.projeto, SUM(contaspagar.valor) AS Total_contaspagar 
                from contaspagar 
                group by projeto) cp on cp.projeto = p.idprojeto 
   LEFT JOIN (select lnota.projeto, SUM(lnota.Valor)  AS Total_lnota 
                from lnota 
                group by projeto) n on n.projeto = p.idprojeto 
   

"Discovering" the problem

For you to find out the cause of the problem, I suggest you run a select without the sums and without the group by to know what values the DBMS is adding up, ie check the data in detail.

    SELECT *
      FROM projeto 
      LEFT JOIN recebimento ON projeto.idprojeto = recebimento.projeto 
      LEFT JOIN contaspagar ON projeto.idprojeto = contaspagar.projeto
      LEFT JOIN lnota ON projeto.idprojeto  = lnota.projeto 
     WHERE projeto.idprojeto = 1

Answer 2

Perhaps that amendment will solve your problem.

I believe the SUM is returning NULL, and the coalesce makes it take the next value that is 0.

The way I did the NULL is already validated before the SUM function, returning the result you want.

    SELECT projeto.Projeto, 
           SUM(isnull(recebimento.Valor, 0)) AS Total, 
           SUM(isnull(contaspagar.Valor, 0)) AS Total2, 
           SUM(isnull(lnota.Valor, 0)) AS Tota3 
    FROM projeto 
    LEFT JOIN recebimento ON recebimento.Projeto = projeto.IdProjeto 
    LEFT JOIN contaspagar ON contaspagar.Projeto = projeto.IdProjeto 
    LEFT JOIN lnota ON lnota.Projeto = projeto.IdProjeto 
    GROUP BY projeto.Projeto