Varios Count in a Mysql sql

Question

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, tried using Union, he returned me normally, but, I can only make a Count.

  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
  )

Answer 1

The way I do it is to create subselects with Join for every totalizer I need and then group it all into an 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 did in the tests is:

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

the link to the table I simulated is here: http://sqlfiddle.com/#! 9/f4007/19