Add separate lines of a child table with values of a parent table without duplicating the values of the parent table

Asked

Viewed 51 times

1

There is a SALE table that generated 2 records in the DOCUMENT table

I needed to show the total sum of value fields of these two lines (showing row by row), but they need to add with some value fields of the SALE table without "duplicating"..

SALES TABLE => ID = 1, OUTROVALOR = 5
TABLE DOCUMENTS:
ID 1, FARE = 15, RATE = 5
ID 2, TARIFF = 8, RATE = 2

SELECT DOCUMENTO.ID, (SUM(DOCUMENTO.TARIFA) + SUM(DOCUMENTO.TAXA) + SUM(VENDA.OUTROVALOR)) AS VALORTOTAL
FROM VENDAS VENDA
JOIN DOCUMENTOS DOCUMENTO ON (DOCUMENTO.VENDA = VENDA.ID)
WHERE VENDA.ID = 1
GROUP BY DOCUMENTO.ID

Upshot:
Row 1 => ID = 1, TOTAL VALUE = 25.
Row 2 => ID = 2, TOTAL VALUE = 15.
That is, the OUTROVALOR field of the SALE table was added both in the first line and in the second line

So my question is: How could I add the OUTROVALOR field in just one of the 2 lines?

Expected result:

Line 1 => ID = 1, TOTAL VALUE = 25. (Added the 5 reais of the field OUTROVALOR 1x)
Line 2 => ID = 2, TOTAL VALUE = 10. (Should not add up the 5 reals again. )

EDIT: I also agree to add 2.50 in each row if it’s easier

sql sql-server
  • 1

    You confirm that the result of your query displayed the line Linha 2 => ID = 2, VALORTOTAL = 15.? Even with ... ON (DOCUMENTO.VENDA = VENDA.ID) WHERE VENDA.ID = 1?

  • Yes, because both the ID 1 and the ID 2 of the DOCUMENTS table have the SALE field = 1 (id), in the case the DOCUMENTS table is a daughter table of SALES and the sale generated 2 DOCUMENTS

1 answer

1

Thiago, follow a suggestion for testing using the operator Apply to count the documents of the sale, and to divide the value between the documents:

select ca.Id, ca.Tarifa + ca.Taxa + v.OutroValor / QtdeDocs as ValorTotal
from Venda as v
cross apply
(
  select d.Id, d.Tarifa, d.Taxa, count(1) over() as QtdeDocs
  from Documentos as d
  where d.Venda = v.Id
) as ca
where v.Id = 1

I hope it helps

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.