CODEWITHSUNDEEP
sqlsql-serverselect
user900360
user900360

Reputation:

Maximum of the count of the grouped elements

Database is AdventureWorks.

select COUNT(*) as 'Number of times a product is sold at same quantity' 
from
  Sales.SalesOrderDetail 
group by 
         OrderQty, ProductID 
order by 
         COUNT(*) desc

This returns data like this: 

Number of times a product is sold at same quantity
--------------------------------------------------
4279
3216
3095
2376
2334
2319
2234
2201
2121
2025
1712
1488
1396
1161
1044

and other 2600+ rows.

I am interested in getting 4279 as output.

I cannot apply Max as the it doesn't work with aggregate function or subquery. I tried anyway. Didn't work.

I am guessing I cannot, since count(*) is not a column. But if there is a way:

How can I get max of such output?

Upvotes: 1

Views: 73

Answers (1)

John Woo
John Woo

Reputation: 263943

just add TOP to limit the number of results

select TOP 1 COUNT(*) as 'Number of times a product is sold at same quantity' 
from  Sales.SalesOrderDetail 
group by  OrderQty, ProductID 
order by  COUNT(*) desc

UPDATE 1

WITH results 
AS
(
  select COUNT(*) as [Number of times a product is sold at same quantity],
         DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) rank_no 
  from   Sales.SalesOrderDetail 
  group   by OrderQty, ProductID 
)
SELECT [Number of times a product is sold at same quantity]
FROM   results
WHERE  rank_no = 2

Upvotes: 2

Related Questions