Group SQL SERVER records using Distinct and Case

It is enough that you group by [detrec].[iddestino] and obtain the sum of the other fields. If a 'NULL' value still appears, you can fix it with IsNull ().

  , sum(case when [enrec].[origenenvios] = [detrec].[iddestino]
        then IsNull([detrec].[nobulto],0) else 0 end) as [nobultorecibido]

Another thing, it is not recommended (although possible), the use of BETWEEN with dates, basically because you have to be truncated the date, and using characters.

select
    [detrec].[iddestino]
  , sum(case when [enrec].[origenenvios] = [detrec].[iddestino]
        then [detrec].[nobulto] else 0 end) as [nobultorecibido]
  , sum(case when [enrec].[origenenvios] = [detrec].[iddestino]
        then [detrec].[peso] else 0 end) as [peso]
  , sum(case when [enrec].[origenenvios] <> [detrec].[iddestino]
        then [detrec].[nobulto] else 0 end) as [nobultoenviado]
  , sum(case when [enrec].[origenenvios] <> [detrec].[iddestino]
        then [detrec].[peso] else 0 end) as [peso2]
from
    [recibos] [enrec]
        inner join [recibosdetalle] [detrec]
            on [detrec].[idrecibo] = [enrec].[idrecibo]
        inner join [destinos] [dest]
            on [dest].[iddestino] = [detrec].[iddestino]
where 
    [enrec].[idservicio] = '1'
    and [fecharecibo] >= cast('2017/01/13' as date)
    and [fecharecibo]  < cast('2017/01/14' as date)
group by
  [detrec].[iddestino];

Without having some data to prove is difficult, but I think the problem is that you have the sum within the condition and when it is null it is like a different value but it separates you. I would try something like this:

sum(
   case when enrec.OrigenEnvios<>detrec.iddestino then DetRec.Nobulto else 0
end) as NoBultoEnviado

You should put it in all case .

try and tell me.

There are several problems with your query. First of all, you do not need to use DISTINCT , if you're already using GROUP BY .

On the other hand, it is not clear, why are you grouping by 3 columns, of which only one is not in some way in an aggregation function.

Your main problem, however, is the fact that you should use the expression CASE within of the aggregation function (in this case, SUM ). Rewriting your query would be:

SELECT  detrec.iddestino,
        DetRec.iddestino, 
        Enrec.origenenvios,
        SUM(CASE 
              WHEN enrec.origenenvios = detrec.iddestino THEN 
              DetRec.nobulto
            END) AS NoBultoRecibido, 
        SUM(CASE 
              WHEN enrec.origenenvios = detrec.iddestino THEN 
              DetRec.peso
            END) AS Peso, 
        SUM(CASE 
              WHEN enrec.origenenvios <> detrec.iddestino THEN 
              DetRec.nobulto 
            END) AS NoBultoEnviado, 
        SUM(CASE 
              WHEN enrec.origenenvios <> detrec.iddestino THEN 
              DetRec.peso
            END) AS Peso 
FROM dbo.Recibos EnRec 
INNER JOIN dbo.Recibosdetalle DetRec 
       ON DetRec.idrecibo = EnRec.idrecibo
INNER JOIN dbo.Destinos Dest 
       ON Dest.iddestino = DetRec.iddestino
WHERE  fecharecibo BETWEEN '20170113' AND '20170113' 
AND enrec.idservicio = '1' 
GROUP  BY EnRec.idservicio, 
          DetRec.iddestino, 
          Enrec.origenenvios;

I took the liberty of adding the schema prefix to the tables, in addition to finishing the query with a ; . I also eliminated the CONVERT in the fecharecibo column, since as it was, you could not use an index if it existed in that column.