Group separate table records

Asked

Viewed 46 times

0

How can I get the number of identical names from 2 tables, customer and suppliers.

Ex: the result is

Name. Qtd

Joao. 2

Peter. 15

Michael. 7

The code I have is this.

Select nome from fornecedores where ativo = 1
Union all
Select nome from clientes  where ativo = 1

I tried the count but it doesn’t work well.

sql

3 answers

1

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

1


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 GROUP BY. So tuples with equal attributes (in this case the attribute nome) are grouped, the COUNT() will return how many tuples obeyed the condition.

1

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;

Browser other questions tagged sql

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