Derived Tables

A derived table is a subquery in the from clause.  It returns a table as its result.  Unlike other subqueries you name them.

In this articles I’ll show you to use derived tables, a how to use their special abilities to work around SQL limitations.

Things you can do with Derived Table Subqueries.

There are several ways to approach a solution in SQL. In many cases, rather than use sub queries, you’ll see you can use an join to “flatten” your subquery. Though this works in many situations, you’ll see derived tables work best in others.

I thought of a couple examples to show you!

Derived Tables and Aggregate Functions

When working with aggregate functions you may have wanted to first summarize some values and then get the overall average.  For instance, suppose you want to know the average bonus given for all territories.

If you run

SELECT   AVG(SUM(Bonus))
FROM     Sales.SalesPerson
GROUP BY Bonus

You’ll get the following error:  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

You may think you can simply run

SELECT AVG(Bonus)
FROM   Sales.SalesPerson

But that calculates the average for a bonus for each salesperson.  To get the average bonus for territories you first have to calculate the total bonus by territory, and then take the average.

One way to do this is with derived tables.  It is used to summarize sales by territory int the following example.  These are then fed into the Average function to obtain an overall average.

SELECT AVG(B.TotalBonus)
FROM   (SELECT   TerritoryID,
                 SUM(Bonus) AS TotalBonus
        FROM     Sales.SalesPerson
        GROUP BY TerritoryID) AS B

Read More: SQL Subqueries – The Ultimate Guide >>

Joining Derived Tables

You can also join two derived tables together!  This can come in handy when you need to aggregate data from two separate tables and combine them together.  In the following example, we’re going to do a comparison of Territory Sales to Territory Sales Quotas.  We’ll do this by summing sales figures and quotas by Territory.

Here are the two Select statements we’ll use to summarize the sales figures:

SELECT   TerritoryID,
         SUM(SalesQuota) AS TerritoryQuota
FROM     Sales.SalesPerson
GROUP BY TerritoryID

And

SELECT   SOH.TerritoryID,
         SUM(SOH.TotalDue) AS TerritorySales
FROM     Sales.SalesOrderHeader AS SOH
GROUP BY SOH.TerritoryID

To obtain the comparison we’ll match these two results together by territory ID.  By using FROM clause subqueries our SQL to do the comparison is

SELECT Quota.TerritoryID,
       Quota.TerritoryQuota,
       Sales.TerritorySales,
       Sales.TerritorySales - Quota.TerritoryQuota
FROM   (SELECT   TerritoryID,
                 SUM(SalesQuota) AS TerritoryQuota
        FROM     Sales.SalesPerson
        GROUP BY TerritoryID) AS Quota
       INNER JOIN
       (SELECT   SOH.TerritoryID,
                 SUM(SOH.TotalDue) AS TerritorySales
        FROM     Sales.SalesOrderHeader AS SOH
        GROUP BY SOH.TerritoryID) AS Sales
       ON Quota.TerritoryID = Sales.TerritoryID

You can see the statement to summarize sales quotas is in red and the statement summarizes actual sales in green.

Final Comments

In many cases what you can do with derived tables, you can do with joins; however, there are special cases where this isn’t the case.  To me, the best explanation is when you need to use two aggregate functions, such as taking the average of a sum.

Keep in mind when writing SQL it is best to go with the simplest and easiest solution, which in my opinion is usually an INNER JOIN, but not every solution is solved as such.  The double aggregation problem is a good example of where a derived table shines.