Início / dba / Perguntas / 325841
Accepted
sql-noob
Asked: 2023-04-11 06:15:53 +0800 CST

Por que minhas linhas estão sendo duplicadas e mostrando a soma incorreta?

  • 772

Eu tenho essas tabelas:

Payments

vendor   | VendorLocation | VendorName   | CheckDate | Amount | TransactionID | InvoiceID
---------------------------------------------------------------------
777      |                | Sony        | 5/1/2020   | 1.50   | 345           | ABC555
777      |                | Sony        | 5/1/2020   | 1.20   | 444           | ABC555
777      |  103           | Sony Music  | 5/1/2020   | 8.50   | 888           | XYZ888
777      |  105           | Sony VR     | 5/1/2020   | 2.50   | 789           | XYZ999
777      |  105           | Sony VR     | 5/1/2020   | 2.50   | 833           | XYZ111

Vendor Location

vendor   | VendorLocation | LocationType |Address 1   | State | City
---------------------------------------------------------------------
777      |  103           | X            |123 Ave      | CA    | Rivertown
777      |  105           | Z            |666 Ave      | CA    | Northtown
777      |  106           | Z            |888 Ave      | CA    | Southtown

Vendor Address

vendor   | VendorLocation | Address 1  | State | City
---------------------------------------------------------------------
777      |                | 1 Main St  | CA    | Rivertown
777      |  103           | 123 Ave    | CA    | Rivertown
777      |  105           | 666 Ave    | CA    | Northtown

O que eu quero que seja a saída:

Vendor Spending

vendor   | VendorLocation | LocationType  | VendorName | TotalTransactions|TotalSpend| Address 1
---------------------------------------------------------------------
777      |                | Z             | Sony       |  2                | $2.70   | 1 Main St
777      |  105           | Z             | Sony VR    |  2                | $5.00   | 666 Ave

No entanto, estou obtendo o resultado abaixo, que é duplicado e o valor totalSpend incorreto:

Vendor Spending

vendor   | VendorLocation | LocationType  | VendorName | TotalTransactions|TotalSpend| Address 1
---------------------------------------------------------------------
777      |                | Z             | Sony       |  2                | $2.70   | 1 Main St
777      |  105           | Z             | Sony VR    |  2                | $7.70   | 1 Main St


This is query I have:

select distinct
ap.vendor Vendor_ID
,vl.VendorLocation
,vl.LocationType
,ap.VendorName
,count(ap.transactionID) as TotalTransactions
,sum( ap.amount) as TotalSpend
,ad.Address1
from payments ap
join  VendorLocation vl
on ( (vl.vendor=ap.vendor and vl.VendorLocation=ap.VendorLocation)
or (vl.vendor=ap.vendor and vl.VendorLocation='')
or (vl.vendor=ap.vendor and ap.VendorLocation='')
and vl.LocationType in ('Z')
) join VENDORADDRESS ad
on (ad.VENDOR=ap.vendor AND ad.VendorLocation=ap.VendorLocation)
where
vl.LocationType in ('Z') and
ap.CheckDate>='4/1/2022'
group by
vl.vendor
,vl.LOCATION_CODE
,vl.LocationType
,ap.VendorName
,ad.Address1
sql-server-2016

1 respostas

  1. Best Answer

    Você tem algumas tabelas bastante desnormalizadas. Repetir os mesmos pontos de dados em várias tabelas (como Address 1) é redundante, ineficiente e, o mais importante, pode levar a uma integridade de dados insatisfatória. Eu recomendo consertar o design da sua mesa.

    Além disso, sua consulta pode ser simplificada assim:

    ;WITH _VendorTransactionTotals AS
(
    SELECT
        vendor, 
        VendorLocation, 
        VendorName,
        COUNT(1) AS TotalTransactions,
        SUM(Amount) AS TotalSpend
    FROM Payments
    WHERE CheckDate >= '4/1/2022'
    GROUP BY vendor, VendorLocation, VendorName
)

SELECT
    VTT.vendor, 
    VTT.VendorLocation,
    VL.LocationType,
    VTT.VendorName,
    VTT.TotalTransactions,
    VTT.TotalSpend,
    VL.[Address 1]
FROM _VendorTransactionTotals AS VTT
INNER JOIN [Vendor Location] AS VL
    ON VTT.vendor = VL.vendor
    AND VTT.VendorLocation = VL.VendorLocation
WHERE VL.LocationType = 'Z'

    Ao utilizar um CTE para agregar sua Paymentstabela primeiro, isso a desduplica antes de ingressar na Vendor Locationtabela. Isso também torna a consulta mais legível e simplifica o código.

    Observe que você também deve usar nomes padrão para tabelas e colunas que não tenham caracteres ímpares , como espaços.

    A propósito, o problema com sua consulta original provavelmente está aqui:

    or (vl.vendor=ap.vendor and vl.VendorLocation='')
or (vl.vendor=ap.vendor and ap.VendorLocation='')
and

    Isso significa que qualquer linha da Vendor Locationtabela com um espaço em branco VendorLocation pode se unir a todas as linhas com o mesmo vendorna Paymentstabela e, vice-versa, qualquer linha da Paymentstabela com um espaço em branco VendorLocation pode se juntar a todas as linhas com o mesmo Vendorna Vendor Locationtabela.

    Além disso, não está claro como a Vendor Addresstabela se relaciona com a Vendor Locationtabela, especialmente para o endereço em branco. Como você sabe 1 Main Stse relaciona LocationType = 'Z'? Esclarecer isso provavelmente ajudará a determinar se seu objetivo é possível e como melhor alcançá-lo.

    • 1

relate perguntas