Solved

SQL-- Max of count

Posted on 2015-01-30
9
284 Views
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.
0
Comment
Question by:BBRRGG
[X]
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
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40580989
No attachment
0
 

Author Comment

by:BBRRGG
ID: 40580995
File now attached.
0
 

Author Comment

by:BBRRGG
ID: 40581060
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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.
0
 

Author Comment

by:BBRRGG
ID: 40581077
Almost.  

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
0
 
LVL 10

Accepted Solution

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

http://www.w3resource.com/sql/aggregate-functions/max-count.php

Basically using a couple inner queries with a having clause
0
 

Author Comment

by:BBRRGG
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.
0
 
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
0
 

Author Closing Comment

by:BBRRGG
ID: 40581101
Great, thanks!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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