SQL DIVISION With Examples

Division in SQL looks deceptively easy. Here's the syntax:

It gets tricky though, because depending on the values of and , you'll get unexpected results. To demonstrate this, let's compare SQL division output vs. Excel division output.

In SQL, the division operator treats integers (whole numbers like 1, -69, 420) differently than in Excel. In SQL, integer division discards the remainder from the output, providing only the integer (whole number) part of the result. This behavior contrasts with Excel, which retains the decimal part.

How to Divide Integers and Return Decimal/Float Output?

You can still make SQL achieve decimal output with a few tricks such as:

• function
• multiplying by
• being explicit with types using

We'll cover these 3 methods next, but before that, a quick word of caution: For simplicity, we'll use the words float, decimal, and double synonymously to represent a number like 3.141596.

The actual differences in the data-types used to represent non-whole numbers is beyond the scope of this tutorial, and quite frankly not needed for most Data Analyst & Data Science use cases.

Divide Integers using CAST()

The function converts one or both operands into decimal or floating-point data types (aka not whole number integers). By casting the operands, you explicitly instruct SQL to consider the division with a different data type, resulting in the desired output.

Example

Result

Divide Integers Using Multiplication by 1.0

Another ingenious technique involves multiplying one of the operands by 1.0. This straightforward action converts an integer into a decimal or floating-point data type, allowing for the inclusion of decimal places in the result.

Example

Result

By multiplying an integer or expression by 1.0, you effortlessly transform it into a decimal or floating-point data type.

Divide Integers using ::DECIMAL/::FLOAT

The notation is a versatile tool to cast data types explicitly. When used for division, it signifies that you want the division to be executed with the specified data type, effectively achieving decimal or floating-point output.

Example

Result

Google SQL Interview Question Using Division & Casting

With your newfound understanding of SQL division, and it's associated challenges, let's tackle a real Google SQL Interview question asked in a Data Analyst interview which tests your skills in division, rounding, and casting!

The interview question gives you the table which looks like this:

Example Input:

Given the input, write a SQL query to calculate the return on ad spend (ROAS) for each advertiser across all ad campaigns. Round your answer to 2 decimal places, and order your output by the field.

Hint #1: ROAS (return on ad spend) is calculated as an ad's $revenue/spend$.

Hint #2: Utilize in order to compute this ROAS metric for each .

What's Next: Handling Nulls

In a perfect world, we have data in every row, of every column. But missing data is reality, which we represent with . So, how do we do division (and more generally arithmetic) if sometimes we have missing data?

That's covered in the next tutorial on handling null values in SQL!