# 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)

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.

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.