Preview
Video Player
00:00
00:00
Using a Subquery to Create a Temporary Table (Part 2)
5:33Learn more about temporary, or derived tables.
In our last video, we were introduced to
something called the temporary table or
0:00
derived table.
0:04
In this video, we'll show a more
complex use of the derived table.
0:06
In our last example,
0:11
we used a derived table to filter results
down to cars from the 2015 model year,
0:12
similar to what we could do with
an IN statement in a WHERE clause.
0:18
For this example,
we want to see the data pivoted so
0:22
that it appears in columns side by side
instead of being listed straight down.
0:25
We can use derived tables for this too.
0:31
Okay, first, I want to see the sum of
sale amounts by sales rep and location.
0:34
To do this, we will use the tables sale,
0:41
sales rep, and location.
0:45
Sale will provide the data
that we want to sum,
0:48
the sale amount, and
we'll alias that as SaleAmount.
0:52
Sales rep and location will provide the
contextual data that we want to group by,
0:56
the LastName and the LocationName.
1:02
We can get a good start like getting the
decide data by using a standard GROUP BY
1:04
statement like this, but
remember we want to display
1:09
the results as a comparison with
the locations in the columns side by side.
1:15
We have two locations with sales data,
Saint Louis and Columbia.
1:20
Let's break out the data into separate
queries and rejoin them as sub queries.
1:26
First, we'll start off with a simple
listing of sales reps as our base query.
1:30
Now, we want to create a query that will
return all sales from the sale table,
1:44
for the Saint Louis location
grouped by sales rep ID.
1:49
First, we need to SalesRepID.
1:54
Then, we want to sum the sale amount.
2:04
And alias it as StLouisAmount.
2:09
They St Louis location
is the location ID of 1.
2:22
Finally, let's GROUP BY the SalesRepID.
2:33
This will now total the sales for each
sales rep at the Saint Louis location.
2:43
Let's do the same for
the Columbia location.
2:49
Let's copy and paste this query and
replace Saint Louis with Columbia.
2:54
And the LocationID should be 2.
3:01
We can use these queries as subqueries.
3:04
We can use LEFT OUTER JOIN so
we don't lose any sales reps.
3:08
Let's allies sub query say that we can
refer to them elsewhere in the main query.
3:22
Let's name the first sub query Loc1 for
location one and Loc2 for location two.
3:27
Let's cut and paste each of
the queries in our subqueries.
3:34
Now, let's add the join criteria for both.
4:11
This SalesRep table has a SalesRepID,
so that's ideal to join them.
4:35
Let's run this query now.
4:58
And make sure you've got
the right table names.
5:04
Now we have the desired layout.
5:07
A side by side comparison of location
cells breaking out by sales reps, nice.
5:09
When building complex queries like this,
always feel free to
5:16
experiment with single queries first,
then add complexity like we just did.
5:20
You're not expected to have this
all figure out in your head first
5:25
before writing a single query like this.
5:29