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

How to Calculate Percentages?

Calculating percentages in SQL is a common task especially in data analysis and reporting. Here's how you can calculate percentages using SQL.

Calculate Percentages

The basic formula to calculate a percentage is . In SQL, you can apply this formula using the following syntax:

Syntax

Let's illustrate this with an example. Suppose we have a table with actual and target sales. We want to calculate the percentage of actual sales achieved against the target sales.

Example

This query calculates the percentage of actual sales achieved against the target sales, rounding the result to two decimal places.

Result

Calculate Percentages with Rounding

Suppose you want to round the percentages to a specified number of decimal places represented by , you can use the following syntax:

Syntax

• : Optional parameter that specifies the number of decimal places to round the result to. If you need a refresher on function, refer to our tutorial.

Using the table above, let's calculate the percentage of actual sales achieved against the target sales, rounding the result to two decimal places.

Example

Result

What's the Correct Way of Displaying Percentages? 0.50 or 50.0?

Both ways of displaying percentages, "0.50" and "50.0" are correct and commonly used. However, the choice between them depends on the context and personal preference.

• "0.50": This format represents the percentage as a decimal, where 0.50 is equivalent to 50%. This format is commonly used in mathematical calculations or when precision is important.
• "50.0": This format represents the percentage with one decimal place, where 50.0 is also equivalent to 50%. This format is often used in reports or visualizations to make the percentage values more readable and understandable to a wider audience.

In SQL, you can choose the appropriate format based on your needs. You can use the function to round the percentage to a specific number of decimal places if necessary. For example, rounding "0.50" to one decimal place would result in "50.0".

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!