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;