Galera, na consulta abaixo está trazendo da seguinte forma.

UsuID    Consultor   Tentativa 1 Abertos 95093    Gabriela         96 

Porém, é era para trazer da seguinte forma:

UsuID    Consultor   Tentativa 2 Abertos 95093    Gabriela         48 

OBS: Quando eu executo uma das subconsultas por fora, os valores trazem normalmente, porém, quando eu junto com inner join, alguns valores são duplicados conforme exemplo acima.

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