In addition to querying raw data with SQL, you can also use math expressions to transform column values. Here's an example:
Let's dive into each arithmetic operator, one by one.
The operator adds two numbers.
The operator subtracts one column value from another.
The operator multiplies two numbers.
Note: this is exactly the same as ... so don't get confused by 's two uses!!
The operator divides the first column value by the number in the 2nd column.
Always ensure you're not dividing by zero, as it will cause an error. We dive into more nuances and issues with SQL division in a later tutorial.
The operator, also known as the modulus or remainder function, returns the remainder of a division operation.
In the above example, the DBMS returns 5 because 23 divide by 6 is 3, with a remainder of 5.
The modulus () operator is often used to find odd and even values, like in this hard Google SQL Interview Question.
While solving the entire Google problem is too tricky right now because it needs ranking window functions, let's look at a small snippet from the full solution which finds odd values:
The above SQL query looks at the and looks at the remainder when divided by 2.
Odd numbers like 1, 3, 5, when divided by 2, have a remainder of 1, which is why gets us all odd numbered measurements.
The operator, also known as the power operator, raises a number to the power of another number.
The above returns 100.
Here's a summary table that summarizes how the arithmetic operators in SQL work:
| Operator | Description | Example | Result |
|---|---|---|---|
| + | Addition | 15 + 5 | 20 |
| - | Subtraction | 15 - 5 | 10 |
| * | Multiplication | 15 * 5 | 75 |
| / | Division | 15 / 5 | 3 |
| % | Modulus (Remainder of Division) | 14 % 5 | 4 |
| ^ | Exponentiation (Not standard in all DBMS) | 15 ^ 2 | 225 |
| - (as a prefix) | Negation | -15 | -15 |
Just like in standard arithmetic, SQL follows the order of operations of PEMDAS:
Here's some SQL examples of PEMDAS:
| SQL Statement | Result | Explanation |
|---|---|---|
| SELECT 3 + 7 * 2; | 17 | Multiplication comes before addition. |
| SELECT (3 + 7) * 2; | 20 | Parentheses means addition happens first. |
| SELECT 10 / 2 + 3 * 4; | 17 | 10/2 = 5, 3*4=12, so 5 + 12 = 17. |
| SELECT (10 / 2) + (3 * 4); | 17 | Same as above, but more explicit with parens! |
To make your code more readable and less confusing, feel free to use parenthesis to make your SQL math formulas more explicit.
Let's practice combining the arithmetic operators in this lesson to compute some interesting metrics by analzying CVS Pharmacy, JP Morgan, and FAANG stock datasets.
Here's a real SQL interview question asked by CVS Health for a healthcare analytics job: Write a query to find the top 3 most profitable medicines sold, and how much profit they made.
Your output should look like this:
| drug | total_profit |
|---|---|
| Humira | 81515652.55 |
| Keytruda | 11622022.02 |
| Dupixent | 11217052.34 |
Hint #1: Total Profit = Total Sales - Cost of Goods Sold
Hint #2: To find the top 3 drugs, just do a
In this JPMorgan Data Analyst interview question, imagine that you're on the credit card marketing analytics team at Chase.
You're preparing to launch a new credit card, and to gain some insights, you're analyzing how many credit cards were issued each month. Write a query that outputs the name of each credit card and the difference in the number of issued cards between the month with the highest issuance cards and the lowest issuance.
Arrange the results based on the largest disparity like as follows:
| card_name | difference |
|---|---|
| Chase Sapphire Reserve | 30000 |
| Chase Freedom Flex | 15000 |
Hint: You'll want to use the and aggregate functions!
A "big-mover month" is when a stock closes up or down by greater than 10% compared to the price it opened at. For example, when COVID hit and e-commerce became the new normal, Amazon stock in April 2020 had a big-mover month because the price shot up from $96.65 per share at open to $123.70 at close – a 28% increase!
| ticker | date | open | close | percent_change |
|---|---|---|---|---|
| AMZN | 04/01/2020 00:00:00 | 96.65 | 123.70 | 28.0 |
| NFLX | 04/01/2022 00:00:00 | 376.80 | 190.36 | -49.5 |
Netflix stock had a big-mover month in April 2022 in the reverse direction. That month, Netflix reported that the company lost 200k subscribers in Q1, and expected to lose another two million subs in Q2. In Apr'22, Netflix stock opened that month at $376.80 per share, but closed at $190.36, representing a 49.5% loss – yikes!
Display the stocks which had "big-mover months", and how many of those months they had. Order your results from the stocks with the most, to least, "big-mover months".
In the last SQL exercise about big-mover months, in order to figure out if the difference between the open & close price was greater than 10% of the opening price, we used this clunky SQL snippet:
We wrote it this way, because could be a positive or negative number, depending on whether the stock closed at 10% higher OR 10% lower compared to the start of the month. We needed the to account for both cases.
Now, some of you might be thinking...
damn I really wish SQL had an absolute value function rn
If that's your thinking, good job – that's exactly what we need!
We'll cover the function, along with some other powerful SQL commands in the next tutorial on mathematical functions in SQL!
Next Lesson
MATH FUNCTIONS 📈