Deleting Duplicate Values in SQL

Navigation
1

I have a problem with a query, because there is only one field that differs from one data to the other. I have tried to use group by , order by , having , I have researched in several forums, I have tried everything and I can not delete this duplicate data.

The query is as follows: 

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 frete_cobrado . The goal is not to eliminate this data, but to join in just one by adding that freight. I tried to use SUM on this freight but it only doubles the value of each duplicate field.

Here is an example of how it is: 

| 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 the same except for freight charged. Here's 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          |

Freights are added resulting in a single line.

Does anyone know what I can do?

    
asked by anonymous 20.09.2017 / 16:13

2 answers

0

Following your example I was able to do with the following select: 

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

And that?

    
20.09.2017 / 19:25
0

I do not know if I can reproduce exactly what you need because I do not know how the records are in each of these tables, but I believe that you simply group and add the freight to a subconsulta and use it later, p> 

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 look at the FreteEntrada table.

    
20.09.2017 / 19:29
APP_URL of .env register entire URL (beyond the domain) of the application Change dropdown menu through input checkbox