SUM in SQL Server returning wrong values

Question

I'm having some trouble with one specific query in SQL Server 2014.

The sum function returns wrong result.

In my case, my SQL query return only one row, with two fields for example: cost and key.

cost with value 10 and key with value ke890wkw.

I don't know why, but if I run something like

SELECT SUM(COST) TOTAL_COST, RTRIM(KEY) 
FROM TABLE 
WHERE KEY = 'ke890wkw'

it returns 20.

The full and real query with this "bug" (?) its on picture sql2

SELECT 
    SUM(CONVERT(FLOAT, DADOSREQF.compValorFrete)) vlr_total_frete,
    --CONVERT(FLOAT, DADOSREQF.compValorFrete) vlr_total_frete,
    (RTRIM(DADOSFORMF.NUMSOLICITACAO) + RTRIM(DADOSREQF.compCodFornecedor) + 
          RTRIM(DADOSREQF.compCodCondicaoPagamento) +  RTRIM(DADOSREQF.compFrete) + 
          RTRIM(DADOSREQF.compCodTransportadora)) chave
FROM
    FLUIG.DBO.ML001102 DADOSFORMF WITH(NOLOCK)
INNER JOIN 
    FLUIG.DBO.ML001105 DADOSREQF WITH(NOLOCK) ON DADOSREQF.DOCUMENTID = DADOSFORMF.DOCUMENTID 
WHERE
    (RTRIM(DADOSFORMF.NUMSOLICITACAO) + RTRIM(DADOSREQF.compCodFornecedor) + 
        RTRIM(DADOSREQF.compCodCondicaoPagamento) +  RTRIM(DADOSREQF.compFrete) + 
        RTRIM(DADOSREQF.compCodTransportadora)) = '1071002999439000103030F040' 
    AND DADOSFORMF.version = (SELECT MAX(version) 
                              FROM FLUIG.DBO.ML001105 vr 
                              WHERE vr.documentid = DADOSFORMF.documentid)
GROUP BY
    RTRIM(DADOSFORMF.NUMSOLICITACAO),
    RTRIM(DADOSREQF.compCodFornecedor),
    RTRIM(DADOSREQF.compCodCondicaoPagamento),
    RTRIM(DADOSREQF.compFrete),
    RTRIM(DADOSREQF.compCodTransportadora),
    compValorFrete

And this is the same query with an external sum which returns de correct value.

SELECT
    SUM(a.vlr_total_frete),
    a.chave 
FROM
    (SELECT 
        --SUM(CONVERT(FLOAT,    DADOSREQF.compValorFrete)) vlr_total_frete,
        CONVERT(FLOAT,  DADOSREQF.compValorFrete) vlr_total_frete,
        (RTRIM(DADOSFORMF.NUMSOLICITACAO) + RTRIM(DADOSREQF.compCodFornecedor) + RTRIM(DADOSREQF.compCodCondicaoPagamento) +  RTRIM(DADOSREQF.compFrete) + RTRIM(DADOSREQF.compCodTransportadora)) chave
    FROM
        FLUIG.DBO.ML001102 DADOSFORMF WITH(NOLOCK)
    INNER JOIN 
        FLUIG.DBO.ML001105 DADOSREQF WITH(NOLOCK) ON DADOSREQF.DOCUMENTID = DADOSFORMF.DOCUMENTID 
    WHERE
        (RTRIM(DADOSFORMF.NUMSOLICITACAO) + RTRIM(DADOSREQF.compCodFornecedor) + RTRIM(DADOSREQF.compCodCondicaoPagamento) +  RTRIM(DADOSREQF.compFrete) + RTRIM(DADOSREQF.compCodTransportadora)) = '1071002999439000103030F040' 
        AND DADOSFORMF.version = (SELECT MAX(version) 
                                  FROM FLUIG.DBO.ML001105 vr 
                                  WHERE vr.documentid = DADOSFORMF.documentid)
    GROUP BY
        RTRIM(DADOSFORMF.NUMSOLICITACAO),
        RTRIM(DADOSREQF.compCodFornecedor),
        RTRIM(DADOSREQF.compCodCondicaoPagamento),
        RTRIM(DADOSREQF.compFrete),
        RTRIM(DADOSREQF.compCodTransportadora),
        compValorFrete) a
GROUP BY 
    a.chave

And some screenshots of it running on SQL Server 2014.

https://i.sstatic.net/dcGb4.png

https://i.sstatic.net/ww4gp.png https://i.sstatic.net/Sv33l.png

Answer 1

Simplified example of what is happening, consider this table:

col1   |   col2
 A     |    10
 A     |    10

This will return one row:

SELECT col1, col2
FROM table
GROUP BY col1, col2

col1   |   col2
 A     |    10

Adding an outer SUM will apply to that one row:

SELECT SUM(col2)
FROM (SELECT col1, col2
      FROM table
      GROUP BY col1, col2) A

col1   |   col2
 A     |    10

However, adding a SUM directly will consider both rows and double the value:

SELECT col1, SUM(col2)
FROM table
GROUP BY col1

col1   |   col2
 A     |    20

Answer 2

You should see 2 records that are duplicates if you run this code. The reason you are getting 10 and 20 different results is because of these 2 duplicates.

SELECT 
    SUM(CONVERT(FLOAT,  DADOSREQF.compValorFrete)) vlr_total_frete,
    --CONVERT(FLOAT,  DADOSREQF.compValorFrete) vlr_total_frete,
    (RTRIM(DADOSFORMF.NUMSOLICITACAO) + RTRIM(DADOSREQF.compCodFornecedor) + RTRIM(DADOSREQF.compCodCondicaoPagamento) +  RTRIM(DADOSREQF.compFrete) + RTRIM(DADOSREQF.compCodTransportadora)) chave
FROM
    FLUIG.DBO.ML001102 DADOSFORMF WITH(NOLOCK)
INNER JOIN 
    FLUIG.DBO.ML001105 DADOSREQF WITH(NOLOCK) ON DADOSREQF.DOCUMENTID = DADOSFORMF.DOCUMENTID 
WHERE
    (RTRIM(DADOSFORMF.NUMSOLICITACAO) + RTRIM(DADOSREQF.compCodFornecedor) + RTRIM(DADOSREQF.compCodCondicaoPagamento) +  RTRIM(DADOSREQF.compFrete) + RTRIM(DADOSREQF.compCodTransportadora)) = '1071002999439000103030F040' 
    AND DADOSFORMF.version = (SELECT MAX (version) 
                              FROM FLUIG.DBO.ML001105 vr 
                              WHRE vr.documentid = DADOSFORMF.documentid)
GROUP BY
    RTRIM(DADOSFORMF.NUMSOLICITACAO),
    RTRIM(DADOSREQF.compCodFornecedor),
    RTRIM(DADOSREQF.compCodCondicaoPagamento),
    RTRIM(DADOSREQF.compFrete),
    RTRIM(DADOSREQF.compCodTransportadora)
    /* ,compValorFrete */