SUM and GROUP BY duplicating values

Question

SUM and GROUP BY duplicating values

Navigation

#1 by (2 votes)

2

When trying to aggregate and group values, I noticed that SUM and GROUP BY are not behaving the way I expect and I could not identify the cause. Performing the query below

SELECT representantes.cod_representante,
       representantes.nome_abrev,
       (itens_notasfiscais.quantidade_faturada) AS qtde_prod,
       (itens_notasfiscais.quantidade_faturada*itens_notasfiscais.valor_unitario) AS valor_total,
       (itens_notasfiscais.quantidade_faturada*tabela_precos_produtos.valor_canal) AS valor_canal
FROM notasfiscais
INNER JOIN 
(
    (
        (
            (
                (
                    tabela_precos
                INNER JOIN (
                    itens_notasfiscais
                            INNER JOIN tabela_precos_produtos ON itens_notasfiscais.cod_produto=tabela_precos_produtos.cod_produto
                            ) ON tabela_precos.cod_tabela = tabela_precos_produtos.cod_tabela
                )
                INNER JOIN representantes ON itens_notasfiscais.cod_representante = representantes.cod_representante
            )
                INNER JOIN clientes ON (tabela_precos.uf=clientes.estado)
                AND (itens_notasfiscais.cod_cliente=clientes.cod_cliente)
            )
        INNER JOIN usuarios ON usuarios.cod = 38
    )
    INNER JOIN canal_vendas ON clientes.cod_canal = canal_vendas.cod_canal
) ON notasfiscais.cod_notafiscal = itens_notasfiscais.cod_notafiscal
INNER JOIN ac_pedido ap ON (
    ap.fk_Cliente = itens_notasfiscais.cod_cliente
    AND ap.fk_Representante = representantes.cod_representante
    AND ap.pedido_cliente = notasfiscais.pedido_cliente
    AND ap.pedido_representante = notasfiscais.pedido_repres
)
INNER JOIN metas m ON (
    m.cod_produto = tabela_precos_produtos.cod_produto
    AND m.ano = 2016
    AND m.cod_representante IN (representantes.cod_representante)
)
WHERE 
(
    (
        (itens_notasfiscais.valor_unitario) < (CASE month(ap.data_emissao) WHEN 1 THEN m.jan_valor WHEN 2 THEN m.fev_valor WHEN 3 THEN m.mar_valor WHEN 4 THEN m.abr_valor WHEN 5 THEN m.mai_valor WHEN 6 THEN m.jun_valor WHEN 7 THEN m.jul_valor WHEN 8 THEN m.ago_valor WHEN 9 THEN m.stm_valor WHEN 10 THEN m.out_valor WHEN 11 THEN m.nov_valor WHEN 12 THEN m.dez_valor END)
    )
    AND ((tabela_precos_produtos.ate)=999999)
    AND ((tabela_precos_produtos.cod_canal)>=1)
)
AND month(notasfiscais.data_emissao) = 8
AND year(notasfiscais.data_emissao) = 2016
AND notasfiscais.bonificacao = 0
AND representantes.cod_representante IN (SELECT cod_representante FROM usuarios_usuarios WHERE cod_gerente = 38)
AND PATINDEX('%' + CAST(itens_notasfiscais.cod_grupo AS NVARCHAR) + '%',usuarios.grupos) > 0
AND tabela_precos_produtos.cod_canal IN (clientes.cod_canal)
GROUP BY representantes.cod_representante,
         representantes.nome_abrev,
         itens_notasfiscais.valor_unitario,
         itens_notasfiscais.quantidade_faturada,
         tabela_precos_produtos.valor_canal
ORDER BY representantes.cod_representante,
         sum(itens_notasfiscais.quantidade_faturada*itens_notasfiscais.valor_unitario) DESC

I get the following result:

cod_representante |nome_abrev   |qtde_prod |valor_total        |valor_canal        |
------------------|-------------|----------|-------------------|-------------------|
39                |CONVEL REPR. |150.0     |12334.08           |14016.0            |
39                |CONVEL REPR. |500.0     |11221.85           |15000.0            |
39                |CONVEL REPR. |50.0      |3429.8             |3897.5             |
39                |CONVEL REPR. |500.0     |8603.45            |11500.0            |
39                |CONVEL REPR. |150.0     |8187.240000000001  |10396.5            |
39                |CONVEL REPR. |20.0      |1204.54            |1368.8             |
39                |CONVEL REPR. |50.0      |1993.9499999999998 |2532.0             |
39                |CONVEL REPR. |150.0     |5981.849999999999  |7596.0             |
39                |CONVEL REPR. |20.0      |1329.1399999999999 |1687.8             |
39                |CONVEL REPR. |100.0     |4821.52            |4990.0             |
39                |CONVEL REPR. |30.0      |4593.33            |5219.700000000001  |
39                |CONVEL REPR. |30.0      |4545.549           |4828.5             |
39                |CONVEL REPR. |30.0      |1500.309           |1704.8999999999999 |
39                |CONVEL REPR. |30.0      |2057.88            |2338.5             |
39                |CONVEL REPR. |20.0      |1000.21            |1136.6             |
39                |CONVEL REPR. |40.0      |1928.612           |2191.6             |
39                |CONVEL REPR. |20.0      |3814.63            |4269.599999999999  |
39                |CONVEL REPR. |20.0      |900.1099999999999  |1143.0             |
39                |CONVEL REPR. |83.0      |3309.957           |4203.12            |
39                |CONVEL REPR. |20.0      |3062.22            |3479.8             |
39                |CONVEL REPR. |20.0      |1496.1799999999998 |1700.2             |
39                |CONVEL REPR. |70.0      |2791.5299999999997 |3544.8             |
39                |CONVEL REPR. |20.0      |1371.92            |1559.0             |
39                |CONVEL REPR. |50.0      |2729.08            |3465.5             |
39                |CONVEL REPR. |50.0      |2514.62            |3056.5             |
39                |CONVEL REPR. |50.0      |2500.5299999999997 |2841.5             |
39                |CONVEL REPR. |50.0      |2500.52            |2685.0             |
39                |CONVEL REPR. |15.0      |1233.4095          |1401.6             |
39                |CONVEL REPR. |50.0      |2410.75            |2495.0             |
39                |CONVEL REPR. |50.0      |2250.29            |2857.5             |
39                |CONVEL REPR. |20.0      |1111.26            |1262.8             |
39                |CONVEL REPR. |20.0      |1089.6200000000001 |1238.1999999999998 |
39                |CONVEL REPR. |10.0      |1033.83            |1312.8             |
39                |CONVEL REPR. |20.0      |2067.66            |2625.6             |
39                |CONVEL REPR. |40.0      |2000.42            |2148.0             |
39                |CONVEL REPR. |15.0      |996.8595           |1265.85            |
39                |CONVEL REPR. |20.0      |964.3000000000001  |1095.8             |
39                |CONVEL REPR. |5.0       |953.66             |1083.7             |
39                |CONVEL REPR. |41.0      |1845.2296000000001 |2343.15            |
39                |CONVEL REPR. |15.0      |1732.9005000000002 |1941.9             |
39                |CONVEL REPR. |30.0      |1637.451           |2079.3             |
39                |CONVEL REPR. |30.0      |1637.4389999999999 |2079.3             |
39                |CONVEL REPR. |35.0      |1575.1995          |2000.25            |
39                |CONVEL REPR. |15.0      |1550.7495000000001 |1969.2             |
39                |CONVEL REPR. |10.0      |1531.11            |1739.9             |
39                |CONVEL REPR. |20.0      |1371.93            |1559.0             |
39                |CONVEL REPR. |15.0      |660.3195           |838.5              |
39                |CONVEL REPR. |10.0      |639.84             |812.5              |
39                |CONVEL REPR. |15.0      |1269.8595          |1612.5             |
39                |CONVEL REPR. |15.0      |1269.8505          |1612.5             |
39                |CONVEL REPR. |10.0      |581.5699999999999  |738.5              |
39                |CONVEL REPR. |20.0      |1095.25            |1390.8000000000002 |
39                |CONVEL REPR. |20.0      |1091.6299999999999 |1386.2             |
39                |CONVEL REPR. |15.0      |996.8595           |1228.5             |
39                |CONVEL REPR. |15.0      |818.7194999999999  |978.45             |
39                |CONVEL REPR. |15.0      |818.7194999999999  |1039.65            |
39                |CONVEL REPR. |2.0       |381.46             |433.48             |
39                |CONVEL REPR. |5.0       |757.59             |860.9000000000001  |
39                |CONVEL REPR. |3.0       |572.1899999999999  |650.22             |
39                |CONVEL REPR. |10.0      |547.63             |695.4000000000001  |
39                |CONVEL REPR. |5.0       |516.92             |656.4              |
39                |CONVEL REPR. |5.0       |516.9100000000001  |647.3000000000001  |
39                |CONVEL REPR. |5.0       |255.66             |324.65000000000003 |
39                |CONVEL REPR. |10.0      |511.32             |649.3000000000001  |
39                |CONVEL REPR. |3.0       |459.33000000000004 |521.97             |
39                |CONVEL REPR. |3.0       |454.55009999999993 |516.54             |
39                |CONVEL REPR. |5.0       |423.28000000000003 |533.3              |
39                |CONVEL REPR. |5.0       |423.28000000000003 |537.5              |
39                |CONVEL REPR. |5.0       |374.04             |425.05             |
39                |CONVEL REPR. |10.0      |339.33             |430.90000000000003 |
39                |CONVEL REPR. |5.0       |153.96             |195.5              |
39                |CONVEL REPR. |5.0       |277.82             |315.7              |
39                |CONVEL REPR. |5.0       |240.7              |305.65000000000003 |
39                |CONVEL REPR. |5.0       |199.39             |240.95             |
39                |CONVEL REPR. |5.0       |194.32             |246.75             |
39                |CONVEL REPR. |2.0       |61.58              |78.2               |
39                |CONVEL REPR. |1.0       |151.52             |172.18             |
39                |CONVEL REPR. |1.0       |126.3              |160.38             |
41                |E.M. REPRE.  |20.0      |1120.042           |1298.6000000000001 |
41                |E.M. REPRE.  |6.0       |695.9939999999999  |787.6800000000001  |
41                |E.M. REPRE.  |10.0      |580.0              |649.3000000000001  |
41                |E.M. REPRE.  |7.0       |515.3666000000001  |545.65             |
41                |E.M. REPRE.  |10.0      |477.78799999999995 |506.4              |
41                |E.M. REPRE.  |10.0      |455.599            |493.5              |
41                |E.M. REPRE.  |5.0       |326.49299999999994 |342.2              |
41                |E.M. REPRE.  |4.0       |243.3668           |252.56             |
41                |E.M. REPRE.  |3.0       |240.59520000000003 |255.03000000000003 |
41                |E.M. REPRE.  |3.0       |229.97820000000002 |243.75             |
41                |E.M. REPRE.  |3.0       |209.01030000000003 |221.54999999999998 |
41                |E.M. REPRE.  |3.0       |182.517            |185.73             |
41                |E.M. REPRE.  |3.0       |155.0667           |164.37             |
41                |E.M. REPRE.  |2.0       |121.6822           |126.66             |
41                |E.M. REPRE.  |3.0       |161.454            |170.49             |
64                |SESAN        |30.0      |1460.0310000000002 |1854.0             |
64                |SESAN        |15.0      |730.0095           |927.0              |
64                |SESAN        |10.0      |543.84             |618.0              |
64                |SESAN        |10.0      |486.67             |618.0              |
145               |ZANATTA MART |70.0      |4874.099999999999  |4790.8             |

For a quick analysis, let's look at the penultimate representative named here of SESAN , the sum of the third column ( qtde ) would be 65 .

Now adding the SUM and the GROUP BY only by the rep:

SELECT representantes.cod_representante,
       representantes.nome_abrev,
       sum(itens_notasfiscais.quantidade_faturada) AS qtde_prod,
       sum(itens_notasfiscais.quantidade_faturada*itens_notasfiscais.valor_unitario) AS valor_total,
       sum(itens_notasfiscais.quantidade_faturada*tabela_precos_produtos.valor_canal) AS valor_canal

...
GROUP BY representantes.cod_representante,
         representantes.nome_abrev
ORDER BY sum(itens_notasfiscais.quantidade_faturada*itens_notasfiscais.valor_unitario) DESC

The grouped result returns:

cod_representante |nome_abrev   |qtde_prod |valor_total        |valor_canal |
------------------|-------------|----------|-------------------|------------|
39                |CONVEL REPR. |3825.0    |193288.5317        |231683.37   |
41                |E.M. REPRE.  |181.0     |11268.451999999997 |12316.45    |
64                |SESAN        |130.0     |6441.101000000001  |8034.0      |
145               |ZANATTA MART |70.0      |4874.099999999999  |4790.8      |

Let's look again at the case of the penultimate representative, SESAN , values doubled, and instead of column qtde_prod return 65 , returned 130

What could be the cause of this behavior?

sql sql-server

asked by anonymous 18.08.2016 / 15:55

1 answer

Calculate duplicate input value Which scene builder container can I use to make my desktop application responsive?

score 2 · Accepted Answer

Although I do not know the structure of your bank, I can offer you a quick solution for your case.

I see that in your first query, you have the following group by.:

GROUP BY representantes.cod_representante,
         representantes.nome_abrev,
         itens_notasfiscais.valor_unitario,
         itens_notasfiscais.quantidade_faturada,
         tabela_precos_produtos.valor_canal

Then without this GROUP BY, your first query should fetch 8 records with cod_representante = 64 and nome_abrev = SESAN .

So if you add to the following column in your SELECT : COUNT(1) as qtd_registros , you should get a result similar to the following.

cod_representante |nome_abrev   |qtde_prod |valor_total        |valor_canal        |qtd_registros |
------------------|-------------|----------|-------------------|-------------------|--------------|
64                |SESAN        |30.0      |1460.0310000000002 |1854.0             |2             |
64                |SESAN        |15.0      |730.0095           |927.0              |2             |
64                |SESAN        |10.0      |543.84             |618.0              |2             |
64                |SESAN        |10.0      |486.67             |618.0              |2             |

This is because in this case you are not adding up the values.

Then you must transform this first query into a subquery in order to prune the data, then perform the second grouping.

WITH CTE_Representantes AS (
    SELECT representantes.cod_representante,
        representantes.nome_abrev,
        (itens_notasfiscais.quantidade_faturada) AS qtde_prod,
        (itens_notasfiscais.quantidade_faturada*itens_notasfiscais.valor_unitario) AS valor_total,
        (itens_notasfiscais.quantidade_faturada*tabela_precos_produtos.valor_canal) AS valor_canal
    FROM notasfiscais
    INNER JOIN 
    (
        (
            (
                (
                    (
                        tabela_precos
                    INNER JOIN (
                        itens_notasfiscais
                                INNER JOIN tabela_precos_produtos ON itens_notasfiscais.cod_produto=tabela_precos_produtos.cod_produto
                                ) ON tabela_precos.cod_tabela = tabela_precos_produtos.cod_tabela
                    )
                    INNER JOIN representantes ON itens_notasfiscais.cod_representante = representantes.cod_representante
                )
                    INNER JOIN clientes ON (tabela_precos.uf=clientes.estado)
                    AND (itens_notasfiscais.cod_cliente=clientes.cod_cliente)
                )
            INNER JOIN usuarios ON usuarios.cod = 38
        )
        INNER JOIN canal_vendas ON clientes.cod_canal = canal_vendas.cod_canal
    ) ON notasfiscais.cod_notafiscal = itens_notasfiscais.cod_notafiscal
    INNER JOIN ac_pedido ap ON (
        ap.fk_Cliente = itens_notasfiscais.cod_cliente
        AND ap.fk_Representante = representantes.cod_representante
        AND ap.pedido_cliente = notasfiscais.pedido_cliente
        AND ap.pedido_representante = notasfiscais.pedido_repres
    )
    INNER JOIN metas m ON (
        m.cod_produto = tabela_precos_produtos.cod_produto
        AND m.ano = 2016
        AND m.cod_representante IN (representantes.cod_representante)
    )
    WHERE 
    (
        (
            (itens_notasfiscais.valor_unitario) < (CASE month(ap.data_emissao) WHEN 1 THEN m.jan_valor WHEN 2 THEN m.fev_valor WHEN 3 THEN m.mar_valor WHEN 4 THEN m.abr_valor WHEN 5 THEN m.mai_valor WHEN 6 THEN m.jun_valor WHEN 7 THEN m.jul_valor WHEN 8 THEN m.ago_valor WHEN 9 THEN m.stm_valor WHEN 10 THEN m.out_valor WHEN 11 THEN m.nov_valor WHEN 12 THEN m.dez_valor END)
        )
        AND ((tabela_precos_produtos.ate)=999999)
        AND ((tabela_precos_produtos.cod_canal)>=1)
    )
    AND month(notasfiscais.data_emissao) = 8
    AND year(notasfiscais.data_emissao) = 2016
    AND notasfiscais.bonificacao = 0
    AND representantes.cod_representante IN (SELECT cod_representante FROM usuarios_usuarios WHERE cod_gerente = 38)
    AND PATINDEX('%' + CAST(itens_notasfiscais.cod_grupo AS NVARCHAR) + '%',usuarios.grupos) > 0
    AND tabela_precos_produtos.cod_canal IN (clientes.cod_canal)
    GROUP BY representantes.cod_representante,
             representantes.nome_abrev,
             itens_notasfiscais.valor_unitario,
             itens_notasfiscais.quantidade_faturada,
             tabela_precos_produtos.valor_canal
)

SELECT cod_representante,
       nome_abrev,
       sum(qtde_prod) AS qtde_prod,
       sum(valor_total) AS valor_total,
       sum(qvalor_canal) AS valor_canal
GROUP BY cod_representante,
         nome_abrev
ORDER BY sum(valor_total) DESC

Personal Note

I found it strange to the following construction:

    INNER JOIN 
    (
        (
            (
                (
                    (
                        tabela_precos
                    INNER JOIN (
                        itens_notasfiscais
                                INNER JOIN tabela_precos_produtos ON itens_notasfiscais.cod_produto=tabela_precos_produtos.cod_produto
                                ) ON tabela_precos.cod_tabela = tabela_precos_produtos.cod_tabela
                    )
                    INNER JOIN representantes ON itens_notasfiscais.cod_representante = representantes.cod_representante
                )
                    INNER JOIN clientes ON (tabela_precos.uf=clientes.estado)
                    AND (itens_notasfiscais.cod_cliente=clientes.cod_cliente)
                )
            INNER JOIN usuarios ON usuarios.cod = 38
        )
        INNER JOIN canal_vendas ON clientes.cod_canal = canal_vendas.cod_canal
    ) ON notasfiscais.cod_notafiscal = itens_notasfiscais.cod_notafiscal

These Inner Joins are at least confusing, I believe that here would fit a refactory in favor of your "future self" or some other programmer.

Additional Explanation

As I said "This is because in this case you are not adding up the values.", this is because in the first query you are using GROUP BY to not display single values.

If you remove Group By, you might have something of the sort:

cod_representante |nome_abrev   |qtde_prod |valor_total        |valor_canal        |
------------------|-------------|----------|-------------------|-------------------|
64                |SESAN        |30.0      |1460.0310000000002 |1854.0             |
64                |SESAN        |30.0      |1460.0310000000002 |1854.0             |
64                |SESAN        |15.0      |730.0095           |927.0              |
64                |SESAN        |15.0      |730.0095           |927.0              |
64                |SESAN        |10.0      |543.84             |618.0              |
64                |SESAN        |10.0      |543.84             |618.0              |
64                |SESAN        |10.0      |486.67             |618.0              |
64                |SESAN        |10.0      |486.67             |618.0              |

When you apply GROUP BY you are not adding up the values, you are only ignoring the duplications. At no point did you say that this behavior is a problem, even because it is the behavior you want.

But on your second query, you were adding up all the values, when in fact you would like to ignore the duplicates, then perform the sum.