开发者

MySQL query help on joins and counting most occurance

开发者 https://www.devze.com 2023-03-25 22:43 出处:网络
I have two tables, client_table and order_table Entries in the client_table are unique with id as primary key

I have two tables, client_table and order_table

Entries in the client_table are unique with id as primary key In the order_table, the order_id is the primary key and also a client_id field. The client_id is the primary key of the client_table. There can be multiple entries in the order_table that contain the same client_id. So it is a one-to-many relationship.

I'm trying to come up with a query that produce the most occurring client_id in the order_table

I have tried the following queries.

SELECT a.id FROM `client_table` as a  inner join order_开发者_开发知识库table as b GROUP BY a.id ORDER BY count(b.client_id)

So I'm looking for a result of client_id which have the most orders. I suppose I only need the order_table and don't need the client_table at all right ?


SELECT b.client_id, count(*) AS Clients
FROM order_table AS b 
GROUP BY b.client_id 
ORDER BY Clients DESC

No need for a join if you only want the clients id, like you said. Also, if you want to join, like in your example query, you need to specify on which column with a ON clause.

... inner join order_table as b on a.id=b.client_id ...


Yes, you don't need the client table at all:

select
    client_id,
    count(order_id) order_count
from
    order_table
group by
    client_id
order by
    count(order_id) DESC


you can try:

SELECT a.id FROM `client_table` as a  left join order_table as b ON a.id = b.client_id GROUP BY a.id ORDER BY count(b.client_id) desc

Use DESC at ORDER BY to get most occurances. Right now you only get the lowest occurances, and try changing INNER JOIN to LEFT JOIN.

0

精彩评论

暂无评论...
验证码 换一张
取 消