MySQL - Find Client with most transactions on a table

Question

I have a table of transactions that stores the transaction id, the client id and the total amount of each transaction. How do I find the client with more transactions? Im using PHP and Mysql, but im sure there is a way to do this inside the SQL query. Thanks.

Answer 1

There's lots of ways to do it, here's one

SELECT COUNT(client_id) AS transaction_count, client_id 
    FROM transactions
    GROUP BY client_id
    ORDER BY COUNT(client_id) DESC
    LIMIT 1

Answer 2

SELECT * FROM Transactions ORDER BY amount DESC LIMIT 1

Answer 3

One solution is:

SELECT `client_id`, COUNT(*) AS c_num 
FROM `transaction` 
GROUP BY `client_id` 
ORDER BY `c_num` ASC
LIMIT 1