Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Reporting with SQL

Well done!

You have completed Reporting with SQL!

Sign up for Treehouse Back to Library
Preview
Video Player
00:00
00:00
00:00
  1. 2x 2x
  2. 1.75x 1.75x
  3. 1.5x 1.5x
  4. 1.25x 1.25x
  5. 1.1x 1.1x
  6. 1x 1x
  7. 0.75x 0.75x
  8. 0.5x 0.5x
Use Up/Down Arrow keys to increase or decrease volume.

Performing Math on Numeric Types

4:35 with Andrew Chalkley

Operators aren't only for comparing values or concatenating strings. They can be used to perform mathematical operations.

In your SQL learning journey you've come across several operators. 0:01
We've seen [SOUND] the equality and inequality operators. 0:05
You've seen comparison operators that test the relationships between two values. 0:09
The operators include less than, greater than, less than or 0:16
equal to, and greater than or equal to. 0:20
You've also seen the concatenation operator, for adding text types together. 0:23
Now, let's turn to operators that can perform mathematical transformations 0:29
on numeric types. 0:34
Firstly, there's the Addition operator that adds two numbers together. 0:36
This is represented by a plus symbol. 0:41
This may be good if you want to add a credit card processing fee to an invoice 0:44
you sent to your clients. 0:47
Next, the Subtraction operator. 0:49
This is represented by a minus symbol. 0:53
You may wanna do this if you're applying a coupon code or a fixed discount. 0:56
The Multiplication operator is represented by a star or asterisk. 1:01
This is great for calculating cells text or interest on a payment. 1:07
And finally, there's the Division operator which is represented by a forward slash. 1:12
Division could be used to calculate equal monthly payments. 1:18
Let's see these operators in action. 1:22
In an earlier video, 1:25
I showed you that select statements can simply output a value. 1:26
For example, select Hello gives you the word Hello. 1:30
You can do this to test SQL's mathematical operators. 1:35
I have set up some examples in the SQL playground on this page. 1:40
Go ahead and open it up and follow along. 1:44
First, there's Addition. 1:47
When we run these statements we see 5 and 7 as you'd expect. 1:49
In Subtraction, select 5- 3, select 12- 20, 1:54
when we run these, we get 2 and -8. 2:01
That's fairly straightforward. 2:06
Next, Multiplication. 2:07
When we run these statements, we get 10 and 30, that's what you'd expect. 2:11
Finally, there's Division. 2:18
Without running these, can you guess what results of all of these statements will be? 2:20
Let's Run it and see. 2:26
2, 2.5, and 2.5. 2:27
The reason the first value is 2 is because of 2:31
the way computers handle whole numbers, or integers. 2:36
If a number doesn't have a decimal place, most programming languages, 2:40
including SQL, will drop the remainder. 2:44
The fractional value that normally appears right after the decimal point. 2:47
In other words, the result rounded down. 2:52
In order to make sure the division provides a floating point number, or 2:56
a number with a decimal place. 3:00
You need to include at least one number with a decimal place. 3:02
Let's see how one of these mathematical operations can be applied 3:05
in a real world example. 3:10
Here's the products table again. 3:13
The state of Florida adds sales tax to all sales in brick and mortar stores. 3:16
Let's modify this statement to include the sales tax in the price. 3:22
Let's multiply the price by 1.06. 3:27
This is 6% sales tax, and provide a reader friendly label, 3:30
Price in Florida, using the AS keyword. 3:35
When you use an arithmetic operator on a column it uniformly applies it to each 3:42
value in that column. 3:47
Let's see this in action. 3:49
The way that floating point numbers in programming languages like SQL 3:51
generate this bizarre looking remainders like this. 3:55
The reason for this is beyond the scope of this course, but 3:58
to fix it, you can use a function called round. 4:01
The round function takes in two arguments. 4:05
The value you want to round and the number of decimal places you want to round it to. 4:08
In our example, we can use the price manipulation as the first argument. 4:14
And the number two for the number of decimal places as the second argument. 4:21
When we run this now, 4:29
the values returned are more in line with what we were expecting. 4:30