Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
- Counting Results 4:41
- Counting Results 2 objectives
- Counting Groups of Rows 2:21
- Counting Groups 2 objectives
- Getting the Grand Total 5:31
- Summing Values 1 objective
- Calculating Averages 2:01
- Averaging Values 1 objective
- Getting Minimum and Maximum Values 2:08
- Calculating the Minimum and Maximum Values 1 objective
- Performing Math on Numeric Types 4:35
- Performing Math 1 objective
- Practice Session 2:31
- Review: Aggregate and Numeric Functions 5 questions
Preview
Video Player
00:00
00:00
00:00
- 2x 2x
- 1.75x 1.75x
- 1.5x 1.5x
- 1.25x 1.25x
- 1.1x 1.1x
- 1x 1x
- 0.75x 0.75x
- 0.5x 0.5x
Operators aren't only for comparing values or concatenating strings. They can be used to perform mathematical operations.
Mathematical Operators
-
*
Multiply -
/
Divide -
+
Add -
-
Subtract
SELECT <numeric column> <mathematical operator> <numeric value> FROM <table>;
Cheat Sheets
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up