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

Database:

Operators:

Table of Contents

## 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_id | total_order_payment | item_count |
---|---|---|

1 | 2 | 4 |

2 | 15 | 4 |

3 | 56 | 2 |

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_id | average_item_cost |
---|---|

1 | 0.8 |

2 | 3.75 |

3 | 28 |

## 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_id | average_item_cost |
---|---|

1 | 0 |

2 | 3 |

3 | 28 |

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(`

. In the example, the data type is *expression* AS *datatype*)`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_id | average_item_cost |
---|---|

1 | 0.5 |

2 | 3.75 |

3 | 28 |

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