SQL-- Max of count

Posted on 2015-01-30
Medium Priority
Last Modified: 2015-01-30
Need SQL that provides the maximum number of items, & also the name of the maximum.

In the attached file, please provide SQL that outputs the following:

10  Fruit

This desired output is the max number of items in a category, and also that category name.
Question by:BBRRGG
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 66

Expert Comment

by:Jim Horn
ID: 40580989
No attachment

Author Comment

ID: 40580995
File now attached.

Author Comment

ID: 40581060
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 10

Expert Comment

by:Jeffrey Dake
ID: 40581069
Select count(*)  , category  from table group by category order by count(*) desc;

This will give you all the counts, starting with the most.  I think that is what you are looking for.

Author Comment

ID: 40581077

But later I need to do a Common Table Expression that joins on Category, and that join needs to filter the table to only fruit (or whichever category has the max # of items).  Thus, need to show only one record, which is the max category, such as:
10    Fruit
LVL 10

Accepted Solution

Jeffrey Dake earned 2000 total points
ID: 40581082
If that is the case I think the second example here should work for you.


Basically using a couple inner queries with a having clause

Author Comment

ID: 40581084
To simplify my explanation, output needs to be only one record:

10  Fruit

Preferably one select statement, but if need a Common Table Expression to accomplish, that'd be OK too.
LVL 10

Expert Comment

by:Jeffrey Dake
ID: 40581088
Select category, count(*) from table group by category having count(*) = (select max(mycount) from (select category, count(*) from table group by category));

I think that is what you want. If not sorry I couldnt help

Author Closing Comment

ID: 40581101
Great, thanks!

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question