Lesson 7 of 12 · 22 min
Derived tables: a subquery in FROM
By the end of this lesson you can write a derived table (a subquery in FROM), use it to aggregate an aggregate, and pre-aggregate one-to-many tables before joining them so totals are not multiplied.
The result of any query is a table: rows and columns. So you can put a whole query in the FROM clause and query its result as if it were a table. That inner query is called a derived table (some tools say inline view). It is the third shape of subquery: lesson 1's returned a value, lesson 2's a list, this one a table.
The classic reason to need one is a question with two levels of aggregation. What is the average order value? Order value isn't stored: you first add up each order's lines (one aggregation, per order), then average those order values (a second aggregation, over orders).
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons