Objective

Write a SQL query to find the most frequently occurring item count in orders (the mode).

Table Schema:

• orders

Task Requirements:

• Count how often each item_count value appears across all orders
• Identify the item_count value(s) with the highest frequency
• If multiple item counts share the same highest frequency, return all of them
• Sort the results by item_count in ascending order
  
  

Output columns: item_count

WITH item_frequencies AS (
    SELECT
        item_count,
        COUNT(*) AS frequency
    FROM orders
    GROUP BY item_count
)
SELECT item_count
FROM item_frequencies
WHERE frequency = (SELECT MAX(frequency) FROM item_frequencies)
ORDER BY item_count;

Explanation

Step 1: Counting Frequencies

WITH item_frequencies AS (
    SELECT
        item_count,
        COUNT(*) AS frequency
    FROM orders
    GROUP BY item_count
)

The CTE groups all orders by their item_count and counts how many orders fall into each group. For example, if 7 orders have 3 items each, the row for item_count = 3 will have frequency = 7.

Step 2: Finding the Maximum Frequency

(SELECT MAX(frequency) FROM item_frequencies)

A subquery scans the CTE to find the single highest frequency value. This serves as the threshold for identifying the mode. It runs once and returns a single number.

Step 3: Filtering for the Mode

WHERE frequency = (SELECT MAX(frequency) FROM item_frequencies)

The WHERE clause keeps only the item_count values whose frequency matches the maximum. If one value has the highest count, only that value is returned. If multiple values are tied at the same highest frequency, all of them pass through. This correctly handles both single-mode and multi-mode scenarios.

Step 4: Sorting the Result

ORDER BY item_count

When there are multiple modes (ties), the output is sorted by item_count in ascending order as required.