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

QuerySQL OutputExcel Output
SELECT 10/422.5
SELECT 10/255
SELECT 10/611.6666666667
SELECT 10.0/42.50000000000000002.5
SELECT 10/3.03.33333333333333333.333333333

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

outputoutputoutputoutput
2.50000000000000002.51.66666666666666671.6666666666666667

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

outputoutputoutputoutput
11.66666666666666671.01.6666666666666667

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

outputoutputoutputoutputoutputoutput
2.50000000000000002.52.50000000000000002.51.66666666666666671.6666666666666667

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

sale_idactual_salestarget_sales
1500.001000.00
2700.00900.00
3850.001100.00
4450.001200.00
51000.001000.00
61200.001000.00

Example

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

Result

sale_idactual_salestarget_salessales_percentage
1500.001000.0050.0000000000
2700.00900.0077.7777777778
3850.001100.0077.2727272727
4450.001200.0037.5000000000
51000.001000.00100.0000000000
61200.001000.00120.0000000000

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

sale_idactual_salestarget_salessales_percentage_rounded
1500.001000.0050.00
2700.00900.0077.78
3850.001100.0077.27
4450.001200.0037.50
51000.001000.00100.00
61200.001000.00120.00

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

If you've subscribed to DataLemur Premium, try this real Google SQL Interview question asked in a Data Analyst interview. The problem tests your skills in division, casting, and rounding (from the last lesson)!

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

Example Input:

1500075003
210009001
33000120002
450020004
51004004

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!

Next Lesson

SQL NULL π«