Sql - Derived tables
Code Life
By Pierre Marsaa
A subquery in FROM
A subquery can also sit in the FROM clause. Its result is used as if it were a table — a derived table.
The average number of badges per trainer, among trainers who have at least one:
↗️
Loading editor...
↶
↷
The inner query produces 3 rows (3, 5, 2); the outer query averages them (3.33). You cannot write AVG(COUNT(*)) directly — aggregates do not nest — so the two steps need two levels.
Always name it
A derived table must always get a name: here AS per_trainer. Without it the query is not valid, and the name is what lets you refer to its columns, like per_trainer.badge_count.
Verify