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