Counting most occurrences of a field's value

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!

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