SQL: Find highest occurrence of a column value in a table?

Question

I'm trying to find the most ordered menu item in a table by counting the occurrences of their item id. I've tried a few different things but I'm pretty lost. This produced the error:

"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

However I think it puts into perspective what I'm trying to achieve:

SELECT count(itemCode) 
FROM OrderProcessing 
WHERE count(itemCode)=max(count(itemCode))

Answer 1

I. modified version of original query (UPDATE after @DanGuzman comment about ties)

select all items with the same count as most wanted item:

SELECT itemCode , count(*) as MaxCount
FROM OrderProcessing
GROUP BY itemCode
HAVING count(*) = 
-- count of most wanted item
(select top 1 count(*) 
 from OrderProcessing 
 group by itemCode 
 order by count(*) desc)

II. the query to select one of most ordered items

SELECT top 1 itemCode --, count(*) as MaxCount  --optional
FROM OrderProcessing
GROUP BY itemCode
ORDER BY count(*) DESC

Answer 2

If you want one row, I would suggest order by:

SELECT TOP 1 itemCode, count(itemCode)
FROM OrderProcessing
GROUP BY itemCode
ORDER BY count(itemCode);

If you want all items with the maximum count, use WITH TIES:

SELECT TOP 1 WITH TIES itemCode, count(itemCode)
FROM OrderProcessing
GROUP BY itemCode
ORDER BY count(itemCode);

If you want to be fancy, use window functions. Here is an example:

SELECT itemCode, cnt
FROM (SELECT itemCode, count(itemCode) as cnt,
             MAX(count(itemCode)) OVER () as maxcnt
      FROM OrderProcessing
      GROUP BY itemCode
     ) op
WHERE cnt = maxcnt;