MySQL: Counting most occurrences of a field's value

Question

Given the following query, how do I return the p_name with the most transactions? And similarly, how do I return the t_amount with the most transactions. I'd like to do it all in this one query of course.

SELECT t.*, p.* 
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id

which can return something like:

t_amount     t_platform      t_user     p_id      p_name

100.00       windows         122         20       simmons
200.00       windows         211         20       simmons
100.00       mac             200         18       smith
100.00       linux           190         20       simmons
100.00       mac             100         18       smith

So given that result set, I'd get back best_partner = simmons and also best_amount = 100.00

Thanks!

Answer 1

SELECT p.*, COUNT(p.id) AS p_count
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY p_count
ORDER BY p_count DESC
LIMIT 1

Answer 2

I assume "best partner"= the parner with the highest number of transactions and "best amount" = the most frequently occurring transaction amount.

To count the transactions you can use the Count() function and group by. Something like this:

SELECT p.name,count(t.id) as transactionCount
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY p.name
ORDER BY 2 DESC 
LIMIT 1

Similar for "best amount":

SELECT t.amount, Count(t.id) as transactionAmountCount
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY t.amount
ORDER BY 2 DESC 
LIMIT 1

Edit: Combined as two sub queries:

SELECT
(SELECT p.name
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY p.name
ORDER BY count(t.id) DESC 
LIMIT 1) as best_partner
,
(SELECT t.amount
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY t.amount
ORDER BY Count(t.id) DESC 
LIMIT 1) as most_occuring_transaction_amount