How can I get the number of identical names from 2 tables, customer and suppliers.
Ex: The result is
Name. Qtd
Joao. 2
Pedro. 15
Miguel. 7
The code I have is the following.
Select nome from fornecedores where ativo = 1
Union all
Select nome from clientes where ativo = 1
I've tried count but it does not work well.
Just use COUNT () together with GROUP BY
Select nome, count(nome) as qtd
from fornecedores
where ativo = 1
group by nome;
Union all
Select nome, count(nome) as qtd
from clientes
where ativo = 1
group by nome;
The COUNT () function is operating over the GROUP BY.
Once tuples with equal attributes (in this case the nome attribute) are grouped, COUNT () returns how many tuples obeyed the condition.
You can try something like this:
SELECT * FROM (
(SELECT nome FROM 'cliente' WHERE ativo = 1) as c,
(SELECT nome FROM 'fornecedor' WHERE ativo = 1) as f
) WHERE c.nome = f.nome
SELECT
aux.nome,
count(1) AS qtd
FROM
(SELECT f.nome FROM fornecedores AS f WHERE f.ativo = 1
UNION ALL
SELECT c.nome FROM clientes AS c WHERE c.ativo = 1 ) AS aux
GROUP BY
aux.nome;
Or simply:
SELECT
nome,
count(1) AS qtd
FROM
(SELECT nome FROM fornecedores WHERE ativo = 1
UNION ALL
SELECT nome FROM clientes WHERE ativo = 1 ) AS aux
GROUP BY
nome;