Filter query by removing duplicates

Question

Good afternoon, I’m with 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

See that there are 2 fields with the codevento 534. I want to remove both but keep the vlrevento for others.

If I remove the vlrevento of GROUP BY He won’t allow me to complete the 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

having count(1)=1

Mensagem 8120, Nível 16, Estado 1, Linha 22
A coluna 'sankhya.TFPFOL.VLREVENTO' é inválida na lista de seleção porque não está contida em uma função de agregação nem na cláusula GROUP BY.

and if I remove the vlrevento select obviously it does not show me in the result

How can I inform the field vlrevento in that select without the appearance of codeventos that have duplicated value?

Grateful from now on

Answer 1

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

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