Filter query by removing duplicates

Question

Filter query by removing duplicates

Navigation

#1 by (2 votes)

0

Good afternoon, I have the following select :

select  codemp, codfunc, codevento
, vlrevento 
from sankhya.TFPFOL
where codemp = 1 
and codfunc = 26
and month(referencia) = 1
and year(referencia) = 2009
group by codemp, codfunc, codevento
, vlrevento
having count(1)=1

That shows me the result below

Noticethatthereare2fieldswithcodevento534.Iwanttoremovebothbutkeepvlreventototheothers.

IfIremovevlreventofromGROUPBYitdoesnotallowmetocompleteSELECT:

selectcodemp,codfunc,codevento,vlreventofromsankhya.TFPFOLwherecodemp=1andcodfunc=26andmonth(referencia)=1andyear(referencia)=2009groupbycodemp,codfunc,codeventohavingcount(1)=1

Mensagem8120,Nível16,Estado1,Linha22Acoluna'sankhya.TFPFOL.VLREVENTO'éinválidanalistadeseleçãoporquenãoestácontidaemumafunçãodeagregaçãonemnacláusulaGROUPBY.

AndifIremovethevlreventofromtheselectobviouslyitdoesnotshowmeintheresult

How can I tell the vlrevento field in this select without showing codeventos that have duplicate value?

Thank you in advance

sql tsql

asked by anonymous 21.12.2018 / 17:34

1 answer

hover with mix-blend-multipy Graphic by ggplot in RStudio v 1.1.463 does not read subtitle and caption in labs ()

score 2 · Accepted Answer

Group the records and if you have more than one record we will remove it from our return using the HAVING clause:

SELECT codemp, codfunc, codevento, SUM(vlrevento)
  FROM sankhya.TFPFOL
 WHERE codemp = 1 
   AND codfunc = 26
   AND month(referencia) = 1
   AND year(referencia) = 2009
 GROUP BY codemp, codfunc, codevento
HAVING count(codemp) = 1