Several Count in a Mysql sql

Question

Several Count in a Mysql sql

Navigation

#1 by (0 votes)

0

I have 2 tables, one called and another client, I need to return how many calls the client had in the current month, last month and last month, I tried using union, it returned me normally, but I can only do a count. p>

  SELECT  
  COUNT(*) AS total_chamado,
  from chamado, cliente
  where
     chamado.id_cliente = cliente.id AND
  GROUP BY chamado.id_cliente
  union
  (
  SELECT
  COUNT(*) AS total_chamado_passado,
  from chamado, cliente
  where
     chamado.id_cliente = cliente.id AND
     chamado.data_atendimento  BETWEEN CURDATE() - INTERVAL 1 month AND CURDATE()
  GROUP BY chamado.id_cliente
  )

php mysql

asked by anonymous 25.07.2015 / 04:44

1 answer

The implementation of BeanValidation is not working HTACCESS - External File [closed]

score 0 · Answer 1

The way I do it is to create subselects with join for each totalizer I need and then group everything into a more external select. In your case, it would look like this:

SELECT cli.id_cliente, total_chamado.total_chamado, total_chamado_passado.total_chamado_passado 
FROM cliente cli
INNER JOIN (SELECT  cliente.id_cliente, COUNT(*) AS total_chamado
            FROM chamado, cliente
            WHERE chamado.id_cliente = cliente.id_cliente
            GROUP BY chamado.id_cliente) AS total_chamado 
ON cli.id_cliente = total_chamado.id_cliente
LEFT JOIN (SELECT cliente.id_cliente, COUNT(*) AS total_chamado_passado
           FROM chamado, cliente
           WHERE chamado.id_cliente = cliente.id_cliente 
             AND chamado.data_atendimento  BETWEEN CURDATE() - INTERVAL 1 month AND CURDATE()
           GROUP BY chamado.id_cliente) AS total_chamado_passado
ON cli.id_cliente = total_chamado_passado.id_cliente

The result I made in the tests stays:

|id_cliente|total_chamado|total_chamado_passado
|     1    |     3       |         2          |
|     3    |     1       |                    |
|     4    |     1       |                    |

The link to the table I've simulated is here: link