# 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;
```