Get the Most Common Purchase Quantity Query Using MS SQL Server?

To find the most common purchase quantity from your OrderDetails table in SQL Server, you need to identify the quantity that occurs most frequently across all orders. This involves grouping by the purchase quantity and counting occurrences, then selecting the quantity with the highest count.

Assuming you have an OrderDetails table with the following columns:

• OrderID
• ProductID
• Quantity (the quantity of the product purchased)

Here’s how you can write the SQL query to find the most common purchase quantity:

SQL Query

WITH QuantityCounts AS (

    SELECT

        Quantity,

        COUNT(*) AS QuantityCount

    FROM OrderDetails

    GROUP BY Quantity

),

MaxQuantityCount AS (

    SELECT

        MAX(QuantityCount) AS MaxCount

    FROM QuantityCounts

)

SELECT

    q.Quantity,

    q.QuantityCount

FROM QuantityCounts q

JOIN MaxQuantityCount m

    ON q.QuantityCount = m.MaxCount;

Explanation:

1. CTE QuantityCounts:

   • COUNT(*) AS QuantityCount: Counts the number of times each quantity appears in the OrderDetails table.
   • GROUP BY Quantity: Groups the rows by the Quantity to aggregate counts.

2. CTE MaxQuantityCount:

   • MAX(QuantityCount) AS MaxCount: Finds the highest count of any quantity from the QuantityCounts CTE.

3. Final Query:

   • JOIN MaxQuantityCount m ON q.QuantityCount = m.MaxCount: Joins the QuantityCounts with MaxQuantityCount to filter out only those quantities that have the highest count.

Example Data and Results

Assume the OrderDetails table has the following data:

OrderID	ProductID	Quantity
1	A	10
2	B	15
3	C	10
4	D	5
5	E	10
6	F	15
7	G	10
8	H	15

In this case:

• Quantities 10 appear 4 times.
• Quantities 15 appear 3 times.
• Quantity 5 appears only once.

The most common purchase quantity is 10 with a count of 4.

The result of the query would be:

Quantity	QuantityCount
10	4

This result shows the most common purchase quantity and how many times it occurs.

You can run this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to find the most common purchase quantity.