Aggregation giving wrong result in T-SQL

Question

I am getting wrong sum of values in more than two tables. Please advise the correct qry for the below

my sample data is as follows.

    IF OBJECT_ID('tempdb..#Departamentos') IS NOT NULL
  DROP TABLE #Departamentos
GO
CREATE TABLE #Departamentos (ID      INT IDENTITY(1,1) PRIMARY KEY,
                            Nome_Dep VARCHAR(200))
GO

INSERT INTO #Departamentos(Nome_Dep) 
VALUES('Vendas'), ('TI'), ('Recursos Humanos')
GO

IF OBJECT_ID('tempdb..#Funcionarios') IS NOT NULL
  DROP TABLE #Funcionarios
GO
CREATE TABLE #Funcionarios (ID      INT IDENTITY(1,1) PRIMARY KEY,
                           ID_Dep  INT,
                           Nome    VARCHAR(200),
                           Salario Numeric(18,2))
GO

INSERT INTO #Funcionarios  (ID_Dep, Nome, Salario) 
VALUES(1, 'Fabiano', 2000), (1, 'Amorim', 2000), (1, 'Diego', 9000),
      (2, 'Felipe', 2000), (2, 'Ferreira', 2500), (2, 'Nogare', 11999),
      (3, 'Laerte', 5000), (3, 'Luciano', 23500), (3, 'Zavaschi', 13999)
GO
IF OBJECT_ID('tempdb..#deals') IS NOT NULL
  DROP TABLE #deals
GO
CREATE TABLE #deals (ID      INT IDENTITY(1,1) PRIMARY KEY,
                           ID_Deal  INT,
                           Nome    VARCHAR(200),
                           Revenue Numeric(18,2))
GO

INSERT INTO #deals  (ID_Deal, Nome, Revenue) 
VALUES(1, 'Fabiano', 50), (1, 'Amorim', 20), (1, 'Diego', 90),
      (2, 'Felipe', 20), (2, 'Ferreira', 25), (2, 'Nogare', 119),
      (3, 'Laerte', 50), (3, 'Luciano', 23), (3, 'Zavaschi', 13)
GO

My query is as follows to get the result:

select d.id,d.Nome_Dep,sum(salario)salario,sum(revenue)revenue from #Departamentos d left join #Funcionarios f on d.ID=f.ID_Dep
left join #deals dd on dd.ID_Deal=d.ID
group by d.id,d.Nome_Dep

But my desired result should be:

ID  Nome_Dep    salario revenue
1   Vendas  13000.00    160.00
2   TI  16499.00    164.00
3   Recursos Humanos    42499.00    86.00

Thanks

Answer 1

There is probably duplication happening because of the double table join. One way around this is to aggregate each table in separate subqueries, and then join to them:

SELECT
    d.id,
    d.Nome_Dep,
    COALESCE(f.sum_salario, 0) AS sum_salario,
    COALESCE(dd.sum_revenue, 0) AS sum_revenue
FROM #Departamentos d
LEFT JOIN
(
    SELECT ID_Dep, SUM(salario) AS sum_salario
    FROM #Funcionarios
    GROUP BY ID_Dep
) f
    ON d.ID = f.ID_Dep
LEFT JOIN
(
    SELECT ID_Deal, SUM(revenue) AS sum_revenue
    FROM #deals
    GROUP BY ID_Deal
) dd
    ON d.ID = dd.ID_Deal;

Demo