- 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
Getting the Grand Total
5:31Calculating grand totals are handy for answering questions like "How much was spent today on the site?" and "What are the total number of goals scored by a particular team?"
We've counted rows but what about
doing something more useful like
0:00
totaling up all the values in a column?
0:04
We're still working with
the e-commerce site database.
0:07
The CEO wants to reward the most
loyal customer with a gift card.
0:10
Let's find out which customer has
spent the most money on our site.
0:15
We're going to use the SUM function.
0:21
The SUM function sums up any numeric column.
0:24
It can be used by conjunction with
the GROUP BY keywords to calculate the sum
0:27
total of a column.
0:32
When you use the SUM
with a GROUP BY keywords,
0:34
you can generate total values
of differently grouped rows.
0:36
For example you could
find out how many items
0:41
you have in stock within
each category in your store.
0:44
Let's take a look at the orders table.
0:48
The main columns we'll be looking at
are the user_id and the cost columns.
0:52
The cost column is the price of
the item at the time of sale.
0:57
Let's use sum to find the total
of all values in the cost column.
1:02
Now this is the total revenue that
the online store has generated.
1:11
This string of numbers after
the decimal point is there because
1:16
of how numbers with fractions
are handled by computers.
1:20
Ignore this for now, but we'll talk
about how to clean this up later on.
1:25
The online store has generated over
nine thousand dollars in revenue.
1:29
How do you think we can get
the total per customer?
1:34
We can group by the user ID.
1:38
This will make sure that the aggregate
function of sum is one on the results for
1:43
each user.
1:48
So let's select the user ID.
1:50
This is so that we can identify each user.
1:53
We could create an alias too to
make it more human readable.
1:57
When we run this now,
we get everybody's total expenditures, but
2:03
remember, the boss wants
to find the best customer.
2:08
To get the person who spent the most,
we need to ORDER BY total_spent DESC.
2:12
Finally, we want to limit it by 1.
2:21
Let's see who this user is.
2:26
We can give the details to the CEO
to send a personal message.
2:41
Upon receiving the information
of the most loyal customer,
2:47
the CEO wants to be more generous.
2:50
The CEO wants to give a gift out to
anyone that spent more than $250.
2:52
Let's modify our query now.
2:57
Okay, this should be simple, right?
3:01
After where we have the table name,
orders, we can use WHERE
3:04
total_spend > 250.
3:08
We can remove the limit too now.
3:14
When we run this, what result do we get?
3:23
Uh-oh, error.
3:26
Misuse of aggregate sum.
3:29
This is because where conditions
filtered the result set or
3:31
rows, before grouping things together.
3:35
It's pointless to group by a user ID or
any column if you're going to
3:39
filter out a certain group of users or
values based on a condition.
3:44
Remember, the sum is working
on the aggregated values.
3:49
In this case, total_spend
is calculated after the grouping.
3:54
So, SQL doesn't know what to do with
it when you put it in a where clause.
4:00
So the question is,
4:06
is there a way to apply conditions
to values that have been aggregated?
4:07
And the answer is yes.
4:13
Using the having key word
4:15
You use the having keyword
after the group by keywords.
4:18
Since the checking happens
to the aggregate values.
4:22
The conditions that you use
with the having keyword
4:26
are exactly the same as what
you'd use with the where keyword.
4:28
When we run this now,
we now get all the user IDs
4:32
of the people who spent over $250.
4:37
To recap, we used the SUM aggregate
function to total up a numeric column.
4:42
In our case, it was the cost column.
4:47
We saw that it could be used with
GROUP BY to get totals of groups of rows.
4:49
And finally, we used the HAVING keyword
to use conditions on aggregate functions.
4:54
It had to be used after
the GROUP BY keywords.
5:01
It's also used before ORDER BY keywords.
5:05
You're getting to be a SQL powerhouse.
5:10
To make sure you've taken it all in,
5:12
I've created some more challenging
assessments for you to take next.
5:14
In the teacher's notes, I've included
cheat sheets to past SQL courses for
5:18
you to use as reference.
5:22
It's not cheating.
5:24
In fact,
many developers use cheat sheets and
5:25
documentation to perform their jobs daily.
5:28