How to Divide Columns in SQL Server, PostgreSQL, or SQLite

Problem:

You want to divide one column by another in SQL Server, PostgreSQL, or SQLite.

Example:

An online store has an orders table with data in the columns order_id, total_order_payment, and item_count.

Let’s say we want to extract the average cost per item for each order, i.e., the total payment for the order divided by the item count.

Solution:

A query to do this is:

SELECT
  order_id,
  CAST(total_order_payment as float)/item_count AS average_item_cost
FROM orders;

And here’s the result:

Discussion:

To divide a column by another in MySQL and Oracle, use the division operator /. Put the first column or expression before the / operator and the second column or expression after the / operator. However, the / operator in SQL Server, PostgreSQL, and SQLite performs the integer division for integer arguments.

If the query is:

SELECT
  order_id,
  total_order_payment / item_count AS average_item_cost
FROM orders;

then its result is:

As you see, all answers are rounded and cast to an integer.

To get the floating-point number result instead of the integer result, you must cast either the dividend or divisor to the floating-point data type. The function CAST() is used in the solution for this reason. This function is written as follows: CAST(expression AS datatype). In the example, the data type is float. You may also use numeric or decimal as data type.

You may use the division operator / to divide by constant values as well. For example, to find out the price with a 50% discount, use this query:

Let’s say we want to extract the average cost per item for each order, i.e., the total payment for the order divided by the item count.

Solution:

A query to do this is:

SELECT
  order_id,
  total_order_payment / item_count AS average_item_cost
FROM orders;

Here’s what you get with this query:

Discussion:

To divide a column by another in MySQL and Oracle, use the division operator /. Put the first column or expression before the / operator and the second column or expression after the / operator.

You may use the division operator / to divide by constant values as well. For example, to find out the price with a 50% discount, use this query:

SELECT 
  order_id, 
  total_order_payment / 2 AS payment_after_discount
FROM orders;