Função Sum Entre Três tabelas

[nytro]

Boa tarde, estou com um pequeno problema em T-SQL no qual gostaria que me desse uma ajudinha, é o seguinte:

Tenho três tabelas:

Obra (ObraID, Descrição)

ObraCustos (ObraCustosID, ObraID, Quantidade, Valor)

ObraProveitos (ObraProveitosID, ObraID, Valor)

O meu objecto é apurar o lucro em cada obra (Total Proveitos - Total Custos), mas no select à algo que faz com que as contas não batam certo:

SELECT DISTINCT Obra.ObraID,
ROUND(SUM(ObraCustos.Quantidade * ObraCustos.Valor),2) TotalCustos,
ROUND(SUM(ObraProveitos.Valor),2) AS TotalProveitos
FROM Obra
INNER JOIN ObraCustos ON ObraCustos.ObraID = Obra.ObraID
INNER JOIN ObraProveitos ON ObraProveitos.ObraID = Obra.ObraID
GROUP BY Obra.ObraID

Ele está a duplicar as linhas e a somar duplamente.

se for fazer os select individualmente dá os valores correctos:

SELECT ObraID, ROUND(SUM(Quantidade * Valor),2) AS ValorCustos FROM ObraCustos
Group BY ObraID

SELECT ObraID, ROUND(SUM(Valor),2) AS ValorProveitos FROM ObraProveitos
Group BY ObraID

Alguem me poderá dar umas luzes ?

Obrigado!

[Rechousa]

Olá,

Se desdobrares a tua query numa uma query "parecida" sem agrupar:

SELECT Obra.ObraID,
ObraCustos.Quantidade * ObraCustos.Valor TotalCustos,
ObraProveitos.Valor AS TotalProveitos
FROM Obra
INNER JOIN ObraCustos ON ObraCustos.ObraID = Obra.ObraID
INNER JOIN ObraProveitos ON ObraProveitos.ObraID = Obra.ObraID

Deverás ter resultados repetidos nos casos em que tenhas mais do que uma linha de proveito ou mais do que uma linha de custo. Aliás, se tiveres uma obra que tenha custos e não tenho proveitos (e ao contrário) a linha nem deve de aparecer.

Solução:

Podes fazer isso com subquery's.

[nytro]

Boas, Obrigado pela dica, eu tinha a ideia que também era por aí. Após consultar alguns sites descobri que se pode fazer joins com subqueries, o que resolveu o meu problema. Deixo aqui a solução, possa alguém ter a mesma dúvida.

SELECT ObraID, Descricao, TotalProveitos, TotalCustos, ROUND((TotalProveitos - TotalCustos),2) AS Resultado
FROM (
SELECT Obra.ObraID, Obra.Descricao, Obra.DataRegisto, ObraCustos2.TotalCustos, ObraProveitos2.TotalProveitos FROM Obra
INNER JOIN
(
 SELECT ObraCustos.ObraID,
 Round(SUM(Quantidade*Valor),2) AS TotalCustos
 FROM
 ObraCustos
 GROUP BY ObraCustos.ObraID
) AS ObraCustos2 ON ObraCustos2.ObraID = Obra.ObraID
INNER JOIN
(
 SELECT
 ObraProveitos.ObraID,
 Round(SUM(Valor),2) AS TotalProveitos
 FROM ObraProveitos
 GROUP BY ObraProveitos.ObraID
) AS ObraProveitos2 ON ObraProveitos2.ObraID = Obra.ObraID
) AS X

Cumprimentos