CODEWITHSUNDEEP
mysqlsqlcount
Justas S
Justas S

Reputation: 594

MySQL Count the rows of which there are most

I have a businesses table. I want to get the owners name who has the most businesses. So far all I only know that I need to use GROUP BY and HAVING.

The problem is I only know the most basic queries...

Upvotes: 0

Views: 32

Answers (2)

Szymon
Szymon

Reputation: 43023

Use GROUP BY and order descending and then take the top one record which is the one that has most businesses:

select OwnerId, count(*) from businesses
group by OwnerId order by count(*) desc
limit 1

Upvotes: 1

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7302

Maybe something like this can help:

select owner, count(*) cntx
  from businesses
 group by owner
 order by cntx desc
 limit 1

Or executing the query without limit 1 clause, and then iterate the result till your needs are satisfied.

Upvotes: 2

Related Questions