Views and Derived Tables

Derived Table

Query Inside FROM

A derived table is a subquery in the FROM clause. It lets one SELECT build rows that an outer SELECT can filter or rename.

Program

Play the script to average sales by category, then filter those derived rows.

derived_table.sql 
CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);
WITH params(min_avg) AS (VALUES ()) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;

CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);
WITH params(min_avg) AS (VALUES ()) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;

CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);
WITH params(min_avg) AS (VALUES ()) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;
derived table `FROM (SELECT ...) AS category_avg` turns a query result into an input table.
outer filter The outer `WHERE` filters rows produced by the inner query.
aggregate first The inner query groups and averages before the outer query filters.