SQL with two different information

Asked

Viewed 70 times

0

I have two queries in the database that work independently, but now the need to join them in a single script.

I made some attempts, but without success. Who can help me, I would very much appreciate.

SELECT 1:

SELECT COUNT(codticket),
       usuario
  FROM vw_tkt_total
 INNER JOIN usuarios ON codusuario = id
 WHERE anofechamento = '$ano'
   AND STATUS IN (3, 13) 
   AND ativo = '1'
 GROUP BY usuario DESC 
 ORDER BY COUNT(codticket) DESC

SELECT 2:

SELECT COUNT(codticket),
       usuario
  FROM vw_tkt_total
 INNER JOIN usuarios ON codusuario = id
 WHERE anoabertura = '$ano'
   AND STATUS IN (1, 2, 4) 
   AND ativo = '1'
 GROUP BY usuario DESC 
 ORDER BY COUNT(codticket) DESC

Changes the status condition of one to the other beyond a catch closing year and another opening year.

mysql sql database

  • From what you’re saying, it seems to be the case mark an answer as accepted. Here we do not write "solved" in the question. If you have an answer that really helped you, mark it as accepted. If you came to the solution on your own, put in the solution as an answer. So content is more organized and easier to find in the future by other people with similar problems.

3 answers

1

Try to use UNION:

SELECT COUNT( codticket ) , usuario 
FROM vw_tkt_total
INNER JOIN usuarios ON codusuario = id
WHERE anofechamento =  '$ano'
AND STATUS IN ( 3, 13 ) 
AND ativo =  '1'
GROUP BY usuario DESC 
UNION
SELECT COUNT( codticket ) , usuario
FROM vw_tkt_total
INNER JOIN usuarios ON codusuario = id
WHERE anoabertura =  '$ano'
AND STATUS IN ( 1, 2, 4) 
AND ativo =  '1'
GROUP BY usuario DESC 
ORDER BY COUNT( codticket ) DESC
  • Important *

Use only 1 order by

1

You can transfer the conditions that are on WHERE for a CASE within the COUNT and thus count only the status you want for each column:

SELECT COUNT(CASE WHEN status IN (3, 13) THEN 1 ELSE NULL END) AS quantidade1,
       COUNT(CASE WHEN status IN (1, 2, 4) THEN 1 ELSE NULL END) AS quantidade2,
       usuario
  FROM vw_tkt_total
       INNER JOIN usuarios ON codusuario = id
 WHERE anofechamento =  '$ano'
   AND ativo =  '1'
 GROUP BY usuario
 ORDER BY 1 DESC, 2 DESC

0

I don’t know if it’s the right way, but I managed to solve it this way.

SELECT x.usuario, SUM( pendente ) pendente, SUM( concluido ) concluido
FROM (

SELECT COUNT( codticket ) PENDENTE, usuario, 0 CONCLUIDO
FROM vw_tkt_total
INNER JOIN usuarios ON codusuario = id
WHERE anoabertura =  '$ano'
AND STATUS IN ( 1, 2, 4 ) 
AND ativo =  '1'
GROUP BY usuario DESC 
UNION ALL 
SELECT 0 PENDENTE, USUARIO, COUNT( codticket ) CONCLUIDO
FROM vw_tkt_total
INNER JOIN usuarios ON codusuario = id
WHERE anofechamento =  '$ano'
AND STATUS IN ( 3, 13 ) 
AND ativo =  '1'
GROUP BY usuario DESC
)x
GROUP BY x.usuario
ORDER BY  `concluido` DESC

Browser other questions tagged mysql sql database

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