Query SQL Server - Union

Asked

Viewed 56 times

1

Guys, the following query below through the Union is bringing in as follows.

Consultor          UsuID     Tentativa 1 Abertos
Gabriela Sousa     95093             17
Gabriela Sousa     95093             32

But I want to bring it this way:

Consultor          UsuID     Tentativa 1 Abertos
Gabriela Sousa     95093             49

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
mysql sql sql-server

2 answers

1

Try to make another select.

select Consultor, UsuID , sun([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
)saida
GROUP BY saida.UsuID, saida.UsuNome

1


The point is that the Union brings really different lines, so you just need to group these results in a single:

SELECT Consultor, UsuID, SUM([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
) as tabela
GROUP BY Consultor, UsuID

  • ball show. Thanks @rLinhares.

Browser other questions tagged mysql sql sql-server

You are not signed in. Login or sign up in order to post.