Henrique M. Henrique M. - 5 months ago 19
SQL Question

MySQL - Group orders count by clients numbers

I want to group order's count to show how many clients have that number of orders.

I have come up with:

select count(*) as quantidade_pedidos, clientes.id
from pedidos
inner join clientes
on pedidos.cliente_id = clientes.id
where pedidos.aprovado = 1
group by quantidade_pedidos


but I just can't group by 'quantidade_pedidos' anyway.

Is there any way to group by a temporary column? Another way of doing this? show how many clients (number) have that number of orders placed?

Example
8 orders placed -> 3 clients have 8 orders placed
etc

Answer

Your original query is wrong. You need to group by clientes.id:

select count(*) as quantidade_pedidos, c.id
from pedidos p inner join
     clientes c
     on p.cliente_id = c.id
where p.aprovado = 1
group by c.id;

In an aggregation query, the unaggregated columns go in the group by, not the aggregated ones.

Also note that table aliases make the query easier to write and to read.

As for the question in the first line, use a subquery:

select quantidade_pedidos, count(*)
from (select count(*) as quantidade_pedidos, c.id
      from pedidos p inner join
           clientes c
           on p.cliente_id = c.id
      where p.aprovado = 1
      group by c.id
     ) x
group by quantidade_pedidos;

But given that the query in the question doesn't work, I'm not sure this is what you really want to do.