How to use group by to compute Max(Count(Columnname)) from Table

How to find data for the following query

Original Source question 89

Table structure: Product(Maker, Model, Type)

Edit Use sqlfiddle for schema

Task: Find out those makers who have the most number of models in the Product table as well as those who have the least number of models. Output: maker, number of models

Hints given in question are GROUP BY, HAVING and IN

I tried various ways to find this basically what I came up with is I need to use Max(COUNT(model))

Result should be

maker   qty 
A        7 --max
C        1 --min

One solution I came up with is

select maker, count(ps.model) as Count from product as ps
group by ps.maker
having count(ps.model) in
(
select max(cnt) from (select count(model) as cnt from product group by maker)t
union
select min(cnt) from (select count(model) as cnt from product group by maker)t
)

Is there any other way.

Answers


If you are using SQL Server 2008, you can simplify it with this: http://www.sqlfiddle.com/#!3/25ba8/7

with counts as
(
  select maker, count(ps.model) as Count from product as ps
  group by ps.maker
)
select cx.*
from counts cx
join
(
  select min(count) as lo, max(count) as hi
  from counts
) as hi_lo 
on cx.count = hi_lo.lo or cx.count = hi_lo.hi;

You can simply...

SELECT *
FROM (
    SELECT TOP 1 maker, COUNT(*) C
    FROM product
    GROUP BY maker
    ORDER BY C DESC
) Q1
UNION
SELECT *
FROM (
    SELECT TOP 1 maker, COUNT(*) C
    FROM product
    GROUP BY maker
    ORDER BY C
) Q2;

...or if you are fond of CTEs...

WITH CTE AS (
    SELECT maker, COUNT(*) C
    FROM product
    GROUP BY maker
)
SELECT *
FROM (
    SELECT TOP 1 *
    FROM CTE
    ORDER BY C DESC
) Q1
UNION
SELECT *
FROM (
    SELECT TOP 1 *
    FROM CTE
    ORDER BY C
) Q2;

...as demonstrated in this SQL Fiddle.


Need Your Help

Is there a way to check if I can use Page.IsValid safely?

asp.net validation

I know that calling Page.IsValid in code-behind of an ASP.NET page throws an exception if I didn't explicitly call Page.Validate before or if validation didn't happen automatically in an event hand...

Passing URL, via UIWebView click, incorrectly

iphone uiwebview uinavigationcontroller

Okay, what I currently have is a TabBarController with five tabs. Each of these tabs are UINavigationControllers. Each of the Views associated with the tabs link to a XIB file that contains a Vie...