1 00:00:00,420 --> 00:00:04,420 In our last video, we were introduced to something called the temporary table or 2 00:00:04,420 --> 00:00:06,160 derived table. 3 00:00:06,160 --> 00:00:10,350 In this video, we'll show a more complex use of the derived table. 4 00:00:11,420 --> 00:00:12,850 In our last example, 5 00:00:12,850 --> 00:00:18,490 we used a derived table to filter results down to cars from the 2015 model year, 6 00:00:18,490 --> 00:00:22,450 similar to what we could do with an IN statement in a WHERE clause. 7 00:00:22,450 --> 00:00:25,650 For this example, we want to see the data pivoted so 8 00:00:25,650 --> 00:00:30,160 that it appears in columns side by side instead of being listed straight down. 9 00:00:31,170 --> 00:00:33,370 We can use derived tables for this too. 10 00:00:34,750 --> 00:00:41,049 Okay, first, I want to see the sum of sale amounts by sales rep and location. 11 00:00:41,049 --> 00:00:45,723 To do this, we will use the tables sale, 12 00:00:45,723 --> 00:00:48,900 sales rep, and location. 13 00:00:48,900 --> 00:00:52,270 Sale will provide the data that we want to sum, 14 00:00:52,270 --> 00:00:56,980 the sale amount, and we'll alias that as SaleAmount. 15 00:00:56,980 --> 00:01:02,410 Sales rep and location will provide the contextual data that we want to group by, 16 00:01:02,410 --> 00:01:04,840 the LastName and the LocationName. 17 00:01:04,840 --> 00:01:09,810 We can get a good start like getting the decide data by using a standard GROUP BY 18 00:01:09,810 --> 00:01:15,010 statement like this, but remember we want to display 19 00:01:15,010 --> 00:01:20,950 the results as a comparison with the locations in the columns side by side. 20 00:01:20,950 --> 00:01:26,000 We have two locations with sales data, Saint Louis and Columbia. 21 00:01:26,000 --> 00:01:30,951 Let's break out the data into separate queries and rejoin them as sub queries. 22 00:01:30,951 --> 00:01:38,557 First, we'll start off with a simple listing of sales reps as our base query. 23 00:01:44,171 --> 00:01:49,855 Now, we want to create a query that will return all sales from the sale table, 24 00:01:49,855 --> 00:01:54,540 for the Saint Louis location grouped by sales rep ID. 25 00:01:54,540 --> 00:01:58,123 First, we need to SalesRepID. 26 00:02:04,022 --> 00:02:06,738 Then, we want to sum the sale amount. 27 00:02:09,995 --> 00:02:14,258 And alias it as StLouisAmount. 28 00:02:22,429 --> 00:02:26,948 They St Louis location is the location ID of 1. 29 00:02:33,516 --> 00:02:36,774 Finally, let's GROUP BY the SalesRepID. 30 00:02:43,165 --> 00:02:48,650 This will now total the sales for each sales rep at the Saint Louis location. 31 00:02:49,690 --> 00:02:52,460 Let's do the same for the Columbia location. 32 00:02:54,820 --> 00:03:01,594 Let's copy and paste this query and replace Saint Louis with Columbia. 33 00:03:01,594 --> 00:03:04,690 And the LocationID should be 2. 34 00:03:04,690 --> 00:03:08,130 We can use these queries as subqueries. 35 00:03:08,130 --> 00:03:12,055 We can use LEFT OUTER JOIN so we don't lose any sales reps. 36 00:03:22,422 --> 00:03:27,800 Let's allies sub query say that we can refer to them elsewhere in the main query. 37 00:03:27,800 --> 00:03:33,350 Let's name the first sub query Loc1 for location one and Loc2 for location two. 38 00:03:34,560 --> 00:03:39,688 Let's cut and paste each of the queries in our subqueries. 39 00:04:11,557 --> 00:04:14,247 Now, let's add the join criteria for both. 40 00:04:35,079 --> 00:04:39,815 This SalesRep table has a SalesRepID, so that's ideal to join them. 41 00:04:58,210 --> 00:04:59,951 Let's run this query now. 42 00:05:04,259 --> 00:05:07,540 And make sure you've got the right table names. 43 00:05:07,540 --> 00:05:09,470 Now we have the desired layout. 44 00:05:09,470 --> 00:05:16,530 A side by side comparison of location cells breaking out by sales reps, nice. 45 00:05:16,530 --> 00:05:20,600 When building complex queries like this, always feel free to 46 00:05:20,600 --> 00:05:25,910 experiment with single queries first, then add complexity like we just did. 47 00:05:25,910 --> 00:05:29,350 You're not expected to have this all figure out in your head first 48 00:05:29,350 --> 00:05:32,640 before writing a single query like this.