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 |
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:
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 π«