SUM and GROUP BY doubling values

Asked

Viewed 920 times

2

While trying to add and group values, I found that the SUM and GROUP BY are not behaving the way I hope and could not identify the cause. Making 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 called here SESAN, the sum of the third column (qtde) would be 65.

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

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 us look again at the case of the penultimate representative, SESAN, the values doubled and instead of the column qtde_prod return 65, returned 130

What could be the cause of this behavior?

sql sql-server

  • I didn’t analyze the SELEC you made. but most likely some JOIN is stuck and bringing duplicate results.

  • @The results are only duplicated when I apply the SUM, the SELECT returns quantities and values correctly. I don’t see how any JOIN may be causing this only with the application of SUM

  • I don’t understand why -1 in the matter...

  • It wasn’t me rs. They scored -1 on a question of mine too...

1 answer

2


Although I’m not familiar with the structure of your bank, I can offer you a quick solution to your case.

I see that in your first consultation, 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

So without this GROUP BY, your first query should bring 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 should transform this first query into a subquery in order to do a data creation, 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 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 confused, I believe that here would fit a refactory in Prou of your "future self" or some other programmer.

Additional Explanation

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

If you remove Group By, you may have something similar:

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 applying the GROUP BY you’re not adding up the figures, you’re just ignoring the duplicates. At no time said that this behavior is a problem, even for being the desired behavior.

but in your second query, you were summing up all the values, when in fact you would like to ignore the duplications, to then carry out the sum.

  • Tobymosque, I believe your solution solved this "behavior", I just didn’t understand the part where you say "This is because in this case you’re not adding up the values." - How is it not adding up?

  • As for the structure, really it is strange and needs a refactory, it is not of my own authorship all the script besides belonging to a legacy system.

  • @Marcelodeandrade edited the reply with an explanation of my quote, I hope you understood.

  • Yes, it’s a lot clearer now, @Tobymosque. Thank you.

Browser other questions tagged sql sql-server

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