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

Solution 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

Solution 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;

Solution 3

I use this query to find items that have highest occurrence:

SELECT t.*
FROM OrderProcessing t
    JOIN
    (SELECT itemCode, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) seq
    FROM OrderProcessing
    GROUP BY itemCode) dt
    ON t.itemCode= dt.itemCode
WHERE dt.seq = 1

And this query to find that count of highest occurrence:

SELECT MAX(cnt) 
FROM (SELECT COUNT(*) cnt 
      FROM OrderProcessing 
      GROUP BY itemCode) dt 

Solution 4

This is another way of doing that:

SELECT OP.itemCode
FROM (
    SELECT OP.itemCode, OP.TotalRows, MAX(TotalRows) OVER() AS MaxRows
    FROM (
        SELECT OP.itemCode, COUNT(*) AS TotalRows
        FROM dbo.OrderProcessing AS OP
        GROUP BY OP.itemCode
    ) AS OP
) AS OP
WHERE OP.TotalRows = OP.MaxRows;

It doesn't look very pretty, but it could result in better execution plan (sort is a really resource-hungry clause).

It also should return you results even if there's more than one Menu Item with same occurence.

Solution 5

A where clause that contains aggregation results is called a having clause. However, you cannot nest aggregation functions: Max( Count( * )).

But you can layer them. The use of CTEs is good for this:

with
Data( ItemID )as(
    select 1 union all
    select 2 union all
    select 2 union all
    select 3 union all
    select 3 union all
    select 3
),
Sums( ItemID, TotalSold )as(
    select  ItemID, Count( * )
    from    Data
    group by ItemID
)
select  *
from    Sums
where   TotalSold =(
    select Max( TotalSold )
    from    Sums );

This will show all items with the most transactions. However, due to the question this query is purported to be answering, I should think that showing all ties would be the correct result. If Items X and Y both sold the most, to submit Item X as the answer, then by implication all other items, including Item Y, sold less. That is not a correct answer. I would discuss this more with your analyst or whomever is asking for this result.

View more solutions

Comments

[ASh]

@DanGuzman, right, top 1 will ignore ties. i should improve solution now

[shA.t]

your queries just return the maximum count of occurrence but OP wants items of that, any way try this SELECT MAX(cnt) FROM (SELECT COUNT(*) cnt FROM OrderProcessing GROUP BY itemCode) dt for that point ;).

[Gordon Linoff]

@shA.t . . . Thank you.