Back to cookbooks list Articles Cookbook

How to Divide one Column by Another in SQL Server, PostgreSQL, or SQLite

  • / (division)
  • CAST

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.

order_idtotal_order_paymentitem_count
124
2154
3562

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:

order_idaverage_item_cost
10.8
23.75
328

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:

order_idaverage_item_cost
10
23
328

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:

order_idaverage_item_cost
10.5
23.75
328

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;

Recommended courses:

Recommended articles:

See also: