Error doing Count on Join

Asked

Viewed 48 times

0

I’m having difficulties in an issue where my codes always go wrong.

The question is: Select the customer name and the number of phones they have registered classified in descending order by the number of phones.

My code:

SELECT DISTINCT clientes.nome ,COUNT(clientes_telefone.telefone) as telefones
from clientes
join clientes_telefone
ON clientes.codigo = clientes_telefone.cliente
Order BY telefone DESC;

I thought the code above would be right but, it shows:

Name Telephone
Brigite 5

I wanted all the names and the number of cell phones to appear, but when I put the count, Everything gets crowded in Brigite.

sql join count

  • I think Group By is missing from your query, and Distinct won’t be necessary

1 answer

2

You must pay attention to two details in your Query:

  1. As commented, the DISTINCT is not necessary; you must replace your idea with a GROUP BY clientes.nome to aggregate the data before sorting;

  2. In the clause Order By, you are calling the "phone" column, not the count COUNT(clientes_telefone.telefone) as telefones (note that this is plural).

Rewriting your query would look like this:

SELECT DISTINCT clientes.nome ,COUNT(clientes_telefone.telefone) as telefones
from clientes join clientes_telefone ON clientes.codigo = clientes_telefone.cliente
GROUP BY clientes.nome
ORDER BY telefones

Browser other questions tagged sql join count

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