How Do I Work Out How Many Cartons I Need in a SQL Query?

This article was inspired after one of our training delegates (Declan!) asked the question “How would you calculate how many egg cartons you need for a given number of eggs?”.

This is a common challenge that we come across. We have an order quantity of a product, we know how many products we can fit in a box, pack or carton, but we want to calculate how many packs we need to be able to ship or deliver the ordered products.

Declan happended to be on a SQL Server SQL basics course when he asked the question, but the challenge is the same whether we are talking about a database environment or an Excel environment. In this article I will show you how to solve the problem in SQL. I will show the Excel version in a separate article

By way of a short example, if I have an order for 60 eggs and the egg carton holds 12 eggs, I know that I need 5 cartons because 12 goes into 60 exactly 6 times. if I have an order for 52 eggs then I will also need 5 cartons as there are 4 whole cartons worth of eggs (48 eggs) and 4 eggs left over so I will have to put them in an extra carton.

For the following examples we will be working with the following data scenario:

SQL Server Solution

Let’s look at how we automate this calculation is SQL Server SQL.

The Product table in our database contains the following data:

The Sale table in our database contains the following data:

The column data types in these tables are shown below:

There are many ways to solve this problem.

The Complicated Way!

I’m including these methods as they demonstrate the use of  the FLOOR function and the Modulus Operator (%).

The following screenshot shows the SQL query which will produce the desired table of results shown above:

The useful function here is FLOOR. The FLOOR functions converts the value passed into it to the previous integer value, in other words we lose the decimal places element of the value, or we ignore the extra eggs that don’t fit in a carton.

      FLOOR([OrderQty] / [ProductPackQty]) AS "Number of Full Cartons",

The useful operator here is the Modulus (%) operator. The Modulus operator carries out a division on the supplied value, but returns the remainder portion only.

[OrderQty] % [ProductPackQty] AS "Number of Eggs Over",

The logic of the final expression uses the Modulus result to determine if an extra carton will be needed:

CASE 
            WHEN [OrderQty] % [ProductPackQty] > 0 
                THEN FLOOR([OrderQty] / [ProductPackQty]) + 1 
            ELSE FLOOR([OrderQty] / [ProductPackQty])
END AS "Total Number of Cartons Needed"

But There is a Simpler Way!

This all looks very clever, but actually there is a more straight forward way of calculating the total number of cartons needed and that is using the CEILKING function as shoiwn in the following version:

The CEILING function converts the value passed to it to the next integer:

CEILING(CAST([OrderQty] AS DECIMAL(8,2)) / [ProductPackQty]) AS "Total Number of Cartons Needed"

So there you go! Two methods to get the same results.

If you would like to know more about SQL Database Querying why not  book yourself on to our SQL Server Database Querying Course?