CODEWITHSUNDEEP
sqlsql-serversum
milton arguello
milton arguello

Reputation: 27

Why is Sum in TOTAL in my sql doubling values?

SELECT dbo.tblTrackNumber.CSTPONBR, dbo.tblTrackNumber.SOPNUMBE,
    CASE WHEN dbo.tblTrackNumber.soptype = '2' THEN 'Order'
         WHEN dbo.tblTrackNumber.soptype = '3' THEN 'Invoice'
         WHEN dbo.tblTrackNumber.soptype = '4' THEN 'Return'
         WHEN tblTrackNumber.soptype = '5' THEN 'BackOrder'
    END AS SOPTYPE,
    SUM(b.subtotal) AS subtotal, b.CSTPONBR AS CUSTPO
FROM [PSOPS].ps01.dbo.SOP30200 AS b RIGHT OUTER JOIN dbo.tblTrackNumber
ON b.CSTPONBR = dbo.tblTrackNumber.CSTPONBR
WHERE (b.VOIDSTTS <> '1') AND (dbo.tblTrackNumber.SOPTYPE = '3')
GROUP BY dbo.tblTrackNumber.CSTPONBR, dbo.tblTrackNumber.SOPNUMBE,
    dbo.tblTrackNumber.SOPTYPE, b.CSTPONBR

Results:

Subtotal values are being doubled (79.60 should be 39.80).

CSTPONBR        SOPNUMBE        SOPTYPE subtotal    CUSTPO
954664-PBM      DTCI000649851   Invoice 79.60000    954664-PBM           
954673-PBM      DTCI000649852   Invoice 65.50000    954673 PBM

Upvotes: 0

Views: 2097

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This usually happens because the join is generating additional rows. The recommended solution is to aggregated the data before doing the join:

SELECT t.CSTPONBR, t.SOPNUMBE,
       (CASE WHEN t.soptype = '2' THEN 'Order'
             WHEN t.soptype = '3' THEN 'Invoice'
             WHEN t.soptype = '4' THEN 'Return'
             WHEN t.soptype = '5' THEN 'BackOrder'
        END) AS SOPTYPE,
    SUM(b.subtotal) AS subtotal, b.CSTPONBR AS CUSTPO
FROM dbo.tblTrackNumber t LEFT JOIN
     (SELECT b.cstponbr, SUM(b.substotal) as subtotal
      FROM [PSOPS].ps01.dbo.SOP30200 b
      GROUP BY b.cstponbr
    ) b
    ON b.CSTPONBR = t.CSTPONBR
WHERE (b.VOIDSTTS <> '1') AND (t.SOPTYPE = '3')
GROUP BY t.CSTPONBR, t.SOPNUMBE, t.SOPTYPE, b.CSTPONBR;

I also made a few other changes:

  • Changed the right join to a left join. Most people find left join easier to follow ("keep all rows in the first table").
  • Added table aliases for the other table.

In addition, you should consider the following:

  • If the ids are numeric, don't enclose the constants for comparison in single quotes. This is misleading. Strings constants for string columns. Numeric constants for number columns.
  • You probably don't need b.CSTPONBR in the output. It should be the same as t.CSTPONBR. You might want a flag if the records match.
  • You probably do not need the group by any more. Without sample data, though, it is hard to be 100% sure.

Upvotes: 2

Related Questions