Eliminating duplicate values in SQL

Asked

Viewed 1,529 times

1

I have a problem in a query, because there is only one field that differs from one data from another. I have tried using group by, order by, having, I searched in several forums, anyway, I tried everything and I can’t delete these duplicated data.

The query is the following: 

SELECT
    FTNF.nr_conhecimento AS conhecimento,
    NFS.nr_nota_fiscal AS NUMERO_NF,
    NFS.tp_registro AS TP_REG,
    NFS.emissao,
    NFS.vl_total,
    NFS.volumes,
    NFS.peso_liquido,
    NFS.peso_bruto,
    NFS.empresa,
    EMP.razão,
    NFS.frete_por_conta,
    FE.vl_frete AS frete_cobrado,
    NFS.cidade_entrega,
    NFS.estado_entrega,
    PV.[Valor do Frete] AS frete_pv,
    (FE.vl_frete / NULLIF(NFS.vl_total,0))*100 AS pc_frete,
    EST.Nome AS estado

FROM FreteEntrada AS FE

INNER JOIN FreteEntradaNotaSaida AS FTNF
        ON (FTNF.nr_conhecimento = FE.nr_conhecimento)
       AND (FTNF.tp_registro = FE.tp_registro)       

INNER JOIN FVFNotaFiscalSaida AS NFS
        ON (NFS.tp_registro = FTNF.tp_registro_nf)
       AND (NFS.nr_nota_fiscal = FTNF.nr_nota)
       AND (NFS.empresa = FTNF.empresa)

INNER JOIN FVFItemNotaFiscalSaida AS INFS
        ON (NFS.nr_sequencial = INFS.nr_sequencial)
       AND (NFS.tp_registro = INFS.tp_registro)

INNER JOIN EMPRESAS AS EMP
        ON (NFS.empresa = EMP.APEL)

INNER JOIN TRANSPORTADORAS AS TP
        ON (FE.cd_transportadora = TP.Código)

LEFT JOIN [Pedidos de Venda] AS PV
       ON (INFS.nr_pedido = PV.Número)
      AND (INFS.tp_pedido = PV.[Tipo de Registro])

LEFT JOIN Estados AS EST
       ON (NFS.estado_entrega = EST.Sigla)

LEFT JOIN Duplicatas AS D
        ON (NFS.nr_nota_fiscal = D.Nota)
       AND (NFS.tp_registro = D.[Tipo])
       AND (NFS.empresa = D.Empresa)

GROUP BY  FTNF.nr_conhecimento,
    NFS.nr_nota_fiscal,
    NFS.tp_registro,
    NFS.emissao,
    NFS.vl_total,
    NFS.volumes,
    NFS.peso_liquido,
    NFS.peso_bruto,
    NFS.empresa,
    EMP.razão,
    NFS.frete_por_conta,
    FE.vl_frete,
    NFS.cidade_entrega,
    NFS.estado_entrega,
    PV.[Valor do Frete],
    EST.Nome

The value that differs is the frete_cobrado. The goal is not to delete this data, but to unite in only one adding up this freight. I tried to use the SUM in that freight but it just doubles the value of each duplicated field.

Here’s an example of how you are:

| Conhecimento | Número NF | Empresa  |  Frete Cobrado |
|--------------|-----------|----------|----------------|
| 5            | 154       | Exemplo  | 55,00          |
| 5            | 154       | Exemplo  | 35,00          |
| 6            | 245       | Exemplo2 | 96,00          |
| 8            | 195       | Exemplo3 | 85,00          |

Note that the first two lines are equal except for the freight charged. This is how it should be: 

| Conhecimento | Número NF | Empresa  |  Frete Cobrado |
|--------------|-----------|----------|----------------|
| 5            | 154       | Exemplo  | 85,00          |
| 6            | 245       | Exemplo2 | 96,00          |
| 8            | 195       | Exemplo3 | 85,00          |

Freight is summed up resulting in a single line.

Someone knows what I can do?

sql sql-server query

  • Can edit your post and place an example table (can be 4 or 5 lines even)? Do this with a table with the data coming and how you’d like it to be.

  • It is possible to mount a fiddle in the Sqlfiddle also.

  • I edited and added the tables, thanks for the suggestion.

  • I believe the column pc_frete also comes with differentiated value, no? Because both pc_frete and frete_cobrado use the field vl_frete table FreteEntrada.

  • He does, but when he’s done frete_cobrado, the pc_frete is corrected.

  • @Pauloabdanur: Problem solved? // What is the SQL Server version?

Show 1 more comment

3 answers

0

I do not know if I can reproduce exactly your need because I do not know how are the records in each of these tables, but I believe that it is enough to group and add the freight in one subconsulta and then use:

SELECT
    FTNF.nr_conhecimento AS conhecimento,
    NFS.nr_nota_fiscal AS NUMERO_NF,
    NFS.tp_registro AS TP_REG,
    NFS.emissao,
    NFS.vl_total,
    NFS.volumes,
    NFS.peso_liquido,
    NFS.peso_bruto,
    NFS.empresa,
    EMP.razão,
    NFS.frete_por_conta,
    FE.vl_frete AS frete_cobrado,
    NFS.cidade_entrega,
    NFS.estado_entrega,
    PV.[Valor do Frete] AS frete_pv,
    (FE.vl_frete / NULLIF(NFS.vl_total,0))*100 AS pc_frete,
    EST.Nome AS estado

FROM (
    SELECT
        FE1.nr_conhecimento,
        FE1.tp_registro,
        FE1.cd_transportadora,
        SUM(FE1.vl_frete) vl_frete
    FROM
        FreteEntrada FE1
    GROUP BY
        FE1.nr_conhecimento,
        FE1.tp_registro,
        FE1.cd_transportadora
) AS FE

INNER JOIN FreteEntradaNotaSaida AS FTNF
        ON (FTNF.nr_conhecimento = FE.nr_conhecimento)
       AND (FTNF.tp_registro = FE.tp_registro)

INNER JOIN FVFNotaFiscalSaida AS NFS
        ON (NFS.tp_registro = FTNF.tp_registro_nf)
       AND (NFS.nr_nota_fiscal = FTNF.nr_nota)
       AND (NFS.empresa = FTNF.empresa)

INNER JOIN FVFItemNotaFiscalSaida AS INFS
        ON (NFS.nr_sequencial = INFS.nr_sequencial)
       AND (NFS.tp_registro = INFS.tp_registro)

INNER JOIN EMPRESAS AS EMP
        ON (NFS.empresa = EMP.APEL)

INNER JOIN TRANSPORTADORAS AS TP
        ON (FE.cd_transportadora = TP.Código)

LEFT JOIN [Pedidos de Venda] AS PV
       ON (INFS.nr_pedido = PV.Número)
      AND (INFS.tp_pedido = PV.[Tipo de Registro])

LEFT JOIN Estados AS EST
       ON (NFS.estado_entrega = EST.Sigla)

LEFT JOIN Duplicatas AS D
        ON (NFS.nr_nota_fiscal = D.Nota)
       AND (NFS.tp_registro = D.[Tipo])
       AND (NFS.empresa = D.Empresa)

GROUP BY  FTNF.nr_conhecimento,
    NFS.nr_nota_fiscal,
    NFS.tp_registro,
    NFS.emissao,
    NFS.vl_total,
    NFS.volumes,
    NFS.peso_liquido,
    NFS.peso_bruto,
    NFS.empresa,
    EMP.razão,
    NFS.frete_por_conta,
    FE.vl_frete,
    NFS.cidade_entrega,
    NFS.estado_entrega,
    PV.[Valor do Frete],
    EST.Nome

The only thing I changed in your query was the way you search for the table FreteEntrada.

  • Very interesting, but unfortunately not solved :(

  • @Paulohenriqueabdanurgomes try to create a Sqlfiddle with the tables used and some sample data and your current query. There are some information that it is not possible to deduce just seeing the query without knowing how the data are.

0

Try exactly like this: 

SELECT
FTNF.nr_conhecimento AS conhecimento,
NFS.nr_nota_fiscal AS NUMERO_NF,
NFS.tp_registro AS TP_REG,
NFS.emissao,
NFS.vl_total,
NFS.volumes,
NFS.peso_liquido,
NFS.peso_bruto,
NFS.empresa,
EMP.razão,
NFS.frete_por_conta,
SUM(FE.vl_frete) AS frete_cobrado,
NFS.cidade_entrega,
NFS.estado_entrega,
PV.[Valor do Frete] AS frete_pv,
(FE.vl_frete / NULLIF(NFS.vl_total,0))*100 AS pc_frete,
EST.Nome AS estado

FROM FreteEntrada AS FE

INNER JOIN FreteEntradaNotaSaida AS FTNF
        ON (FTNF.nr_conhecimento = FE.nr_conhecimento)
       AND (FTNF.tp_registro = FE.tp_registro)       

INNER JOIN FVFNotaFiscalSaida AS NFS
        ON (NFS.tp_registro = FTNF.tp_registro_nf)
       AND (NFS.nr_nota_fiscal = FTNF.nr_nota)
       AND (NFS.empresa = FTNF.empresa)

INNER JOIN FVFItemNotaFiscalSaida AS INFS
        ON (NFS.nr_sequencial = INFS.nr_sequencial)
       AND (NFS.tp_registro = INFS.tp_registro)

INNER JOIN EMPRESAS AS EMP
        ON (NFS.empresa = EMP.APEL)

INNER JOIN TRANSPORTADORAS AS TP
        ON (FE.cd_transportadora = TP.Código)

LEFT JOIN [Pedidos de Venda] AS PV
       ON (INFS.nr_pedido = PV.Número)
      AND (INFS.tp_pedido = PV.[Tipo de Registro])

LEFT JOIN Estados AS EST
       ON (NFS.estado_entrega = EST.Sigla)

LEFT JOIN Duplicatas AS D
        ON (NFS.nr_nota_fiscal = D.Nota)
       AND (NFS.tp_registro = D.[Tipo])
       AND (NFS.empresa = D.Empresa)

GROUP BY  FTNF.nr_conhecimento,
NFS.nr_nota_fiscal,
NFS.tp_registro,
NFS.emissao,
NFS.vl_total,
NFS.volumes,
NFS.peso_liquido,
NFS.peso_bruto,
NFS.empresa,
EMP.razão,
NFS.frete_por_conta,
NFS.cidade_entrega,
NFS.estado_entrega,
PV.[Valor do Frete],
(FE.vl_frete / NULLIF(NFS.vl_total,0))*100,
EST.Nome

-1

Following your example I managed to do with the following select :

SELECT 
    Conhecimento,
    NumeroNF,
    Empresa,
    SUM(FreteCobrado)
FROM Teste
GROUP BY Conhecimento,
    NúmeroNF,
    Empresa

And that ?

  • I had already tried this way and did not give, it may be that for other specific problems solve.

  • 1

    Could you generate the result of your table ? Ow even generate a script ? I’ll see if I can select on my machine with your data

Browser other questions tagged sql sql-server query

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