SELECT MySQL with 02 counts in same table with WHERE and GROUP BY in same Query-CodePudding

I am trying to do 02 COUNTS in same table, using WHERE and GROUP BY, but the result of the last column is coming wrong...

Thats the command SQL which I writed till now:

(SELECT estado_sigla,
        estado,
        Count(*) AS numero_advogados,
        Count(*) AS numero_escritorios
 FROM   advogados_e_escritorios
 WHERE  tipo = '1'
 GROUP  BY estado_sigla)
UNION
(SELECT estado_sigla,
        estado,
        Count(*) AS numero_advogados,
        Count(*) AS numero_escritorios
 FROM   advogados_e_escritorios
 WHERE  tipo = '2'
 GROUP  BY estado_sigla);

This is the output:

INSERT INTO `advogados_e_escritorios` (`estado_sigla`, `estado`, `numero_advogados`, `numero_escritorios`) VALUES
('BA', 'Bahia', 1, 1),
('SP', 'São Paulo', 3, 3),
('SP', 'São Paulo', 1, 1);
COMMIT;

But thats my desire Output (the last column repeating the data from the third, I dont know why):

INSERT INTO `advogados_e_escritorios` (`estado_sigla`, `estado`, `numero_advogados`, `numero_escritorios `) VALUES
('BA', 'Bahia', 1, 0),
('SP', 'São Paulo', 3, 1);
COMMIT;

This is the estructure of my table and the sample content:

CREATE TABLE IF NOT EXISTS `advogados_e_escritorios` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `tipo` int(1) NOT NULL,
  `cpf` varchar(30) NOT NULL,
  `cnpj` varchar(30) NOT NULL,
  `nome` varchar(70) NOT NULL,
  `sobrenome` varchar(70) NOT NULL,
  `sexo` int(1) NOT NULL,
  `nome_do_escritorio` varchar(80) NOT NULL,
  `nome_do_responsavel` varchar(50) NOT NULL,
  `registro_oab` varchar(40) NOT NULL,
  `email` varchar(80) NOT NULL,
  `senha` varchar(60) NOT NULL,
  `imagem_perfil` varchar(80) NOT NULL,
  `slug_perfil` varchar(80) NOT NULL,
  `apresentacao` varchar(80) NOT NULL,
  `telefone_fixo` varchar(20) NOT NULL,
  `telefone_celular` varchar(20) NOT NULL,
  `cep` varchar(15) NOT NULL,
  `estado_sigla` varchar(2) NOT NULL,
  `estado` varchar(120) NOT NULL,
  `cidade` varchar(120) NOT NULL,
  `bairro` varchar(120) NOT NULL,
  `logradouro` varchar(120) NOT NULL,
  `numero` int(10) NOT NULL,
  `complemento` varchar(70) NOT NULL,
  `site` varchar(120) NOT NULL,
  `facebook` varchar(120) NOT NULL,
  `instagram` varchar(120) NOT NULL,
  `linkedin` varchar(120) NOT NULL,
  `numero_de_avaliacoes` int(10) NOT NULL,
  `media_avaliacoes` decimal(10,1) NOT NULL,
  `destaque` int(1) NOT NULL,
  `ativo` int(1) NOT NULL,
  `email_confirmado` int(1) NOT NULL,
  `ultimo_login` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data_criacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data_atualizacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

INSERT INTO `advogados_e_escritorios` (`id`, `tipo`, `cpf`, `cnpj`, `nome`, `sobrenome`, `sexo`, `nome_do_escritorio`, `nome_do_responsavel`, `registro_oab`, `email`, `senha`, `imagem_perfil`, `slug_perfil`, `apresentacao`, `telefone_fixo`, `telefone_celular`, `cep`, `estado_sigla`, `estado`, `cidade`, `bairro`, `logradouro`, `numero`, `complemento`, `site`, `facebook`, `instagram`, `linkedin`, `numero_de_avaliacoes`, `media_avaliacoes`, `destaque`, `ativo`, `email_confirmado`, `ultimo_login`, `data_criacao`, `data_atualizacao`) VALUES
(1, 1, '16459537807', '', 'Silvio', 'Satyro Pelosi', 2, '', '', '151097/SP', '[email protected]', '123', '', '', '', '', '(18) 99999-9999', '', 'SP', 'São Paulo', 'Assis', 'Centro', 'Rua Fernão Dias', 711, 'apto 33', '', '', '', '', 0, '0.0', 1, 1, 0, '2021-10-19 01:20:02', '2021-12-17 20:31:41', '2021-12-21 02:00:09'),
(2, 1, '', '', 'Lucas', 'Karam', 2, '', '', '', '', '', '', '', '', '', '(18) 99999-9999', '', 'BA', 'Bahia', 'Salvador', '', '', 0, '', '', '', '', '', 10, '3.0', 1, 1, 0, '2021-12-20 01:20:02', '2021-12-20 00:45:11', '2021-12-21 02:00:09'),
(3, 1, '16459537807', '', 'Silvio', 'Satyro Pelosi', 2, '', '', '151097/SP', '[email protected]', '123', '', '', '', '', '(18) 99999-9999', '', 'SP', 'São Paulo', 'Assis', 'Centro', 'Rua Fernão Dias', 711, 'apto 33', '', '', '', '', 10, '4.5', 1, 1, 0, '2021-12-21 01:20:02', '2021-12-17 20:31:41', '2021-12-21 02:00:09'),
(4, 2, '16459537807', '', 'Silvio', 'Satyro Pelosi', 2, '', '', '151097/SP', '[email protected]', '123', '', '', '', '', '(18) 99999-9999', '', 'SP', 'São Paulo', 'Assis', 'Centro', 'Rua Fernão Dias', 711, 'apto 33', '', '', '', '', 10, '7.0', 1, 1, 0, '2021-11-10 01:20:02', '2021-11-17 20:31:41', '2021-12-21 02:00:09'),
(5, 1, '16459537807', '', 'Silvio', 'Satyro Pelosi', 1, '', '', '151097/SP', '[email protected]', '123', '', '', '', '', '(18) 99999-9999', '', 'SP', 'São Paulo', 'Assis', 'Centro', 'Rua Fernão Dias', 711, 'apto 33', '', '', '', '', 10, '9.0', 1, 1, 0, '2021-12-21 01:20:02', '2021-12-17 20:31:41', '2021-12-21 02:00:09');
COMMIT;

Could you tell me what I am doing wrong?

CodePudding user response：

You need conditional aggregation:

SELECT estado_sigla, estado,
       COUNT(CASE WHEN tipo = '1' THEN 1 END) AS numero_advogados,
       COUNT(CASE WHEN tipo = '2' THEN 1 END) AS numero_escritorios
FROM advogados_e_escritorios
WHERE tipo IN ('1', '2')
GROUP BY estado_sigla, estado;

or:

SELECT estado_sigla, estado,
       SUM(tipo = '1') AS numero_advogados,
       SUM(tipo = '2') AS numero_escritorios
FROM advogados_e_escritorios
WHERE tipo IN ('1', '2')
GROUP BY estado_sigla, estado;

See the demo.