Galera, in the query below is bringing as follows.
UsuID Consultor Tentativa 1 Abertos 95093 Gabriela 96
However, it is meant to bring the following:
UsuID Consultor Tentativa 2 Abertos 95093 Gabriela 48
When I run one of the subqueries from the outside, the values usually do, but when I join inner join, some values are duplicated as shown 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