Arkadiusz G. Arkadiusz G. - 2 months ago 9
SQL Question

MySQL SELECT COUNT GROUP

I have table "orders" with column ID (id order) and IDU (id user).
I want calculate like this:

50 person have 1 order
30 person have 2 order
etc...


My code (bad)

SELECT DISTNICT count(id), count(idu) FROM orders GROUP BY idu


Please help me :)

Answer

I call this a histogram of histogram queries. You want to know how many users have a given order count. The solution is to use subqueries and group by twice:

select cnt, count(*), min(idu), max(idu)
from (select idu, count(*) as cnt
      from orders
      group by idu
     ) ou
group by cnt;

When running this type of query, I usually include the min and max of the user id, so I can readily find examples of users (typically those that have many orders).