Getting the Grand Total5:31 with Andrew Chalkley
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?"
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 up any numeric column. 0:24 In 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 D. 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 underscore spent is calculated after the grouping. 3:54 So, sequel 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 pass 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 up