Select in two tables with Count

Asked

Viewed 40 times

0

I have two tables in Mysql

clientes
id
nome
cat

categorias
id
nome

I need to create a SELECT, that shows all categories, but only categories that have more than 10 clients (for example).

I wanted it in just one SELECT, but I can’t think what it would be like...

I tried that:

SELECT nome
  FROM categorias
 INNER JOIN clientes
    ON clientes.cat = categorias.id
 GROUP BY categorias.nome
HAVING COUNT(clientes.id) >=10 
  • and already tried something? put in the query that has so far, if the problem is the 10 clients, show the query without this criterion

  • I tried this>: SELECT name FROM categories INNER JOIN clients ON clients.cat = categories.id GROUP BY categories.name having Count (clients.id) >=10

2 answers

1

Your query is almost right, the problem in it is, both tables have a column called "name", and this will give ambiguous reference, you need to prefix which table is using the column:

SELECT categorias.nome 
  FROM categorias 
 INNER JOIN clientes ON clientes.cat = categorias.id 
 GROUP BY categorias.nome 
having count (clientes.id) >=10

You can see working here (I put the quantity 5 to avoid making many Inserts): https://www.db-fiddle.com/f/j8FBajK7tWnaU6oTrKJkV2/0

You can change Count to <= 3 and you’ll find more categories.

  • It generates this error: #1630 - FUNCTION test.Count does not exist. Check the 'Function Name Parsing and Resolution' Section in the Reference Manual

  • see the example working and compare with your code to see what’s wrong, the link shows working, based on the table names and fields you put in the question

0

You can use a subquery in the WHERE to check how many records you have in the table clientes with the category of the line in question:

SELECT cat.nome
  FROM categorias cat
 WHERE 10 <= (SELECT COUNT(1)
                FROM clientes cli
               WHERE cli.cat = cat.id)

Browser other questions tagged

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