Sqlquery - Some Duplicate Records

Question

Guys, in the query below is bringing the following way.

UsuID    Consultor   Tentativa 1 Abertos
95093    Gabriela         96

However, it was to bring in the following way:

UsuID    Consultor   Tentativa 2 Abertos
95093    Gabriela         48

NOTE: When I run one of the sub-consultations on the outside, the values usually bring, however, when I together with Ner Join, some values are duplicated as an example above.

select
s7.UsuID,s7.Consultor,SUM(s7.[Tentativa 1 Abertos]) AS [Tentativa 1 Abertos]
from
(select U.UsuNome Consultor, U.UsuID, (COUNT((S.SolID))) AS [Tentativa 1 Abertos]
    from Solicitacao S 
    left join Usuario U on U.UsuID = S.UsuIDResponsavel
    WHERE U.UsuTipo = 'A' AND U.UsuIDGrupo = 30 AND U.EmpLiberada = 1 and u.UsuUsuario is not null
    and S.SolEstagioID IN (235,276,278,294) AND S.SolStatus IN (0,1,5)-- AND S.UsuIDCliente not in (select Y.UsuIDCliente from Solicitacao Y where Y.SolTipID in (710) and Y.SolStatus in (0,1,5))
    GROUP BY U.UsuID, U.UsuNome

    union

    select U.UsuNome Consultor, U.UsuID, (COUNT((S.TarID))) AS [Tentativa 1 Abertos]
    from Tarefa S 
    left join Usuario U on U.UsuID = S.UsuIDResponsavel
    WHERE U.UsuTipo = 'A' AND U.UsuIDGrupo = 30 AND U.EmpLiberada = 1 and u.UsuUsuario is not null
    and S.TarEstagioID IN (235,276,278,294) AND S.TarStatus IN (0,1,5)-- AND S.UsuIDCliente not in (select Y.UsuIDCliente from Solicitacao Y where Y.SolTipID in (710) and Y.SolStatus in (0,1,5))
    GROUP BY U.UsuID, U.UsuNome) s7


inner join

(select U.UsuNome Consultor, U.UsuID, (COUNT((S.SolID))) AS [Tentativa 2 Abertos]
    from Solicitacao S 
    left join Usuario U on U.UsuID = S.UsuIDResponsavel
    WHERE U.UsuTipo = 'A' AND U.UsuIDGrupo = 30 AND U.EmpLiberada = 1 and u.UsuUsuario is not null
    and S.SolEstagioID IN (236,277,279,295) AND S.SolStatus IN (0,1,5)-- AND S.UsuIDCliente not in (select Y.UsuIDCliente from Solicitacao Y where Y.SolTipID in (710) and Y.SolStatus in (0,1,5))
    GROUP BY U.UsuID, U.UsuNome

    union all

    select U.UsuNome Consultor, U.UsuID, (COUNT((S.TarID))) AS [Tentativa 2 Abertos]
    from Tarefa S 
    left join Usuario U on U.UsuID = S.UsuIDResponsavel
    WHERE U.UsuTipo = 'A' AND U.UsuIDGrupo = 30 AND U.EmpLiberada = 1 and u.UsuUsuario is not null
    and S.TarEstagioID IN (236,277,279,295) AND S.TarStatus IN (0,1,5)-- AND S.UsuIDCliente not in (select Y.UsuIDCliente from Solicitacao Y where Y.SolTipID in (710) and Y.SolStatus in (0,1,5))
    GROUP BY U.UsuID, U.UsuNome)s8

on s7.UsuID = s8.UsuID and s7.Consultor = s8.Consultor
group by s7.UsuID,s7.Consultor

Answer 1

I believe that this duplication is in the second query, where you use the Union all.

One-night stand in that reply but briefly, the Union all considers duplicate items for consultation. I believe that is the point.

Being (only) this, uses in the two internal consultations the Union, so each item will be accounted for only once.