Division In SQL

Question

I am wanting to divide a int quantity by a number, but my output is not what I am after. This is my DDL

Create Table Orders 
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,partid varchar(100) NOT NULL
    ,qtyordered int DEFAULT '0'
    ,orderedby varchar(100) NOT NULL
    ,ordereddate date DEFAULT GETDATE()
)

Insert Into Orders (partid, qtyordered, orderedby) VALUES
('SS100', 10, 'James'), ('RR200', 5, 'Bob'), ('NN300', 3, 'Jake'), ('OO400', 5, 'Blue')

Now I have trired using the SQL Server functions Ceiling() and Floor() but I am not getting my desired output. This is the query I tried, what do I need to do in SQL Server to get an output of 2, 2, 1?

Select
partid
,CEILING(qtyordered/3) As [First] --want to be 2
,CEILING(qtyordered/3) As [Second] --want to be 2
,FLOOR(qtyordered/3) As [Third] --want to be 1
FROM Orders
WHERE partid = 'RR200'

Answer 1

Due 'qtyordered' is an integer, you need to either, CAST/CONVERT it to decimal, or use a decimal/float value. (3.0)

Select
partid, qtyordered
,CEILING(qtyordered/3.0) As [First] --want to be 2
,CEILING(qtyordered/3.0) As [Second] --want to be 2
,FLOOR(qtyordered/3) As [Third] --want to be 1
FROM Orders
WHERE partid = 'RR200'
GO

partid | qtyordered | First | Second | Third
:----- | ---------: | :---- | :----- | ----:
RR200  |          5 | 2     | 2      |     1

dbfiddle here

According to MS DOCS, CEILING returns same type as the numeric expresion:

Return Types
Returns the same type as numeric_expression

Answer 2

I assume that do not have to or want to use CEILING and FLOOR but the aim is to divide the qtyordered value into 3 integer values - that are as close to each other as possible - and that when you then add them you'll get back the original value.

While for value 5 the ceiling-ceiling-floor methods works ok, if qtyordered is 10 (or 4 or 7 or 13 or ...), you'll get 4,4,3 which add back to 11 and not 10!

You can achieve consistent (mathematically) results using only integer arithmetic. The trick is to start from the "third" value, i.e. the value that will be the smallest of the three:

   "Third"  =  qtyordered / 3

then subtract that value from qtyordered and divide by 2:

   "Second" = (qtyordered - "Third") / 2

and then subtract both "Third" and "Second" from qtyordered to find the "First":

   "First"  = qtyordered - "Second" - "Third"  

The query becomes:

SELECT 
   partid,
   qtyordered,
   qtyordered - (qtyordered - qtyordered / 3) / 2   
                            - qtyordered / 3        AS [First], 
                (qtyordered - qtyordered / 3) / 2   AS [Second],
                              qtyordered / 3        AS [Third] 
FROM 
    Orders ;

Test at dbfiddle.uk.

The code and the logic behind it is more clear if we use CROSS APPLY:

SELECT 
   partid,
   qtyordered,
   [First], 
   [Second],
   [Third] 
FROM 
    Orders 
  CROSS APPLY
    ( SELECT [Third]  = (qtyordered)                      / 3 )  AS q3
  CROSS APPLY
    ( SELECT [Second] = (qtyordered - [Third])            / 2 )  AS q2
  CROSS APPLY
    ( SELECT [First]  = (qtyordered - [Third] - [Second]) / 1 )  AS q1
 ;

The expression "First" = qtyordered - "Second" - "Third" assures that the 3 values will add up to the original qtyordered. Combined with the other 2 expressions, it's easy to show that:

"Third" <= "Second" <= "First"

and that the difference "First" - "Third" is either 1 or 0, so the three values are as close as possible to 1/3 or qtyordered.