SELECT with repeated field

Asked

Viewed 674 times

0

I’m having trouble executing an SQL query.

The consultation is:

SELECT DISTINCT CHAVE_FATO,COD_FILIAL,COD_DOCTO,NUM_DOCTO,DATA,VALOR_TOTAL,VALOR_LIQUIDO,COD_CLI_FOR,NOME_CLI_FOR,COD_VEND_COMP,NOME_VEND_COMP,COD_PRODUTO,DESC_PRODUTO_EST,NOME_SECAO,QTDE_UND,QTDE_PRI,QTDE_AUX,VALOR_UNITARIO,VALOR_TOTAL_ITEM 
FROM [SATK].[dbo].entrada 
WHERE DATA > '2018-01-01 00:00:00' 
ORDER BY NUM_DOCTO ASC

Does not work, the return keeps showing repeated values in the NUM_DOCTO column.

I need to eliminate it, but I’m not getting it.

  • Distinct makes the distinction of EVERYTHING. And not just a column

  • You have to group columns by the GROUP BY clause which can be grouped together, others you can use MIN, MAX functions until you arrive at a result that suits you. But all columns have to be dealt with.

2 answers

1


Complementing the response with group by, you need to indicate in repeated case, which will be the chosen record to be displayed. Can be with MIN (smaller), MAX (larger), SUM (sum), AVG (average), COUNT (quantity).

An example would be:


SELECT MAX(CHAVE_FATO),MIN(COD_FILIAL),MAX(COD_DOCTO),NUM_DOCTO,MIN(DATA),MAX(VALOR_TOTAL),MAX(VALOR_LIQUIDO),MAX(COD_CLI_FOR),MAX(NOME_CLI_FOR),MAX(COD_VEND_COMP),MAX(NOME_VEND_COMP),MAX(COD_PRODUTO),MAX(DESC_PRODUTO_EST),MAX(NOME_SECAO),MAX(QTDE_UND),MAX(QTDE_PRI),MAX(QTDE_AUX),MAX(VALOR_UNITARIO),MAX(VALOR_TOTAL_ITEM) 
FROM [SATK].[dbo].entrada 
WHERE DATA > '2018-01-01 00:00:00'
GROUP BY NUM_DOCTO
ORDER BY NUM_DOCTO ASC

But stay tuned, in this case will be displayed only one line. If you have more than one product linked to this document, will come in the result only one respecting the aggregation function (min, max, sum...).

0

Use GROUP BY

SELECT DISTINCT CHAVE_FATO,COD_FILIAL,COD_DOCTO,NUM_DOCTO,DATA,VALOR_TOTAL,VALOR_LIQUIDO,COD_CLI_FOR,NOME_CLI_FOR,COD_VEND_COMP,NOME_VEND_COMP,COD_PRODUTO,DESC_PRODUTO_EST,NOME_SECAO,QTDE_UND,QTDE_PRI,QTDE_AUX,VALOR_UNITARIO,VALOR_TOTAL_ITEM 
FROM [SATK].[dbo].entrada 
WHERE DATA > '2018-01-01 00:00:00'
GROUP BY NUM_DOCTO
ORDER BY NUM_DOCTO ASC

More information on: https://www.w3schools.com/sql/sql_groupby.asp

  • Gives the following error: Message 8120, Level 16, State 1, Line 1 Column 'SATK.dbo.input.CHAVE_FATO' is invalid in the select list because it is not contained either in an Aggregate Function or the GROUP BY clause.

Browser other questions tagged

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