Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Reporting with SQL!
You have completed Reporting with SQL!
Preview
Calculating 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?"
To total up numeric columns use the SUM() function.
SELECT SUM(<numeric column) FROM <table>;
SELECT SUM(<numeric column) AS <alias> FROM <table>
GROUP BY <another column>
HAVING <alias> <operator> <value>;
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
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
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