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.
| Query | SQL Output | Excel Output |
|---|---|---|
| SELECT 10/4 | 2 | 2.5 |
| SELECT 10/2 | 5 | 5 |
| SELECT 10/6 | 1 | 1.6666666667 |
| SELECT 10.0/4 | 2.5000000000000000 | 2.5 |
| SELECT 10/3.0 | 3.3333333333333333 | 3.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.
You can still make SQL achieve decimal output with a few tricks such as:
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.
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
| output | output | output | output |
|---|---|---|---|
| 2.5000000000000000 | 2.5 | 1.6666666666666667 | 1.6666666666666667 |
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
| output | output | output | output |
|---|---|---|---|
| 1 | 1.6666666666666667 | 1.0 | 1.6666666666666667 |
By multiplying an integer or expression by 1.0, you effortlessly transform it into a decimal or floating-point data type.
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
| output | output | output | output | output | output |
|---|---|---|---|---|---|
| 2.5000000000000000 | 2.5 | 2.5000000000000000 | 2.5 | 1.6666666666666667 | 1.6666666666666667 |
Calculating percentages in SQL is a common task especially in data analysis and reporting. Here's how you can calculate percentages using SQL.
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_id | actual_sales | target_sales |
|---|---|---|
| 1 | 500.00 | 1000.00 |
| 2 | 700.00 | 900.00 |
| 3 | 850.00 | 1100.00 |
| 4 | 450.00 | 1200.00 |
| 5 | 1000.00 | 1000.00 |
| 6 | 1200.00 | 1000.00 |
Example
This query calculates the percentage of actual sales achieved against the target sales, rounding the result to two decimal places.
Result
| sale_id | actual_sales | target_sales | sales_percentage |
|---|---|---|---|
| 1 | 500.00 | 1000.00 | 50.0000000000 |
| 2 | 700.00 | 900.00 | 77.7777777778 |
| 3 | 850.00 | 1100.00 | 77.2727272727 |
| 4 | 450.00 | 1200.00 | 37.5000000000 |
| 5 | 1000.00 | 1000.00 | 100.0000000000 |
| 6 | 1200.00 | 1000.00 | 120.0000000000 |
Suppose you want to round the percentages to a specified number of decimal places represented by , you can use the following syntax:
Syntax
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_id | actual_sales | target_sales | sales_percentage_rounded |
|---|---|---|---|
| 1 | 500.00 | 1000.00 | 50.00 |
| 2 | 700.00 | 900.00 | 77.78 |
| 3 | 850.00 | 1100.00 | 77.27 |
| 4 | 450.00 | 1200.00 | 37.50 |
| 5 | 1000.00 | 1000.00 | 100.00 |
| 6 | 1200.00 | 1000.00 | 120.00 |
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.
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".
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:
| campaign_id | spend | revenue | advertiser_id |
|---|---|---|---|
| 1 | 5000 | 7500 | 3 |
| 2 | 1000 | 900 | 1 |
| 3 | 3000 | 12000 | 2 |
| 4 | 500 | 2000 | 4 |
| 5 | 100 | 400 | 4 |
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 .
Hint #2: Utilize in order to compute this ROAS metric for each .
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 🚫