How to create Derived Tables in SQL Server

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.
If you like the video you can support me on Patreon, / beardeddev​
In this SQL tutorial I answer the below questions:
What are derived tables?
When should I use a derived table?
I talk you through how to create derived tables and what there typical use cases are, in the examples I also demonstrate common errors you might get or see when working with derived tables.
Finally I show the execution plans of the queries and explain how derived tables are executed inline and what that actually means.
Code Samples - this is not complete but you can copy and paste and change as necessary
SELECT
AVG(Total)
FROM
(
SELECT
CustomerID,
SUM(TotalDue) AS Total
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
) AS D
-- executed inline
SELECT
CustomerID,
SUM(TotalDue) AS Total
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
SELECT
CustomerID,
SUM(TotalDue) AS Total
FROM
(
SELECT
CustomerID,
TotalDue
FROM Sales.SalesOrderHeader
) AS D
GROUP BY CustomerID;

[@briandennehy6380]

Cheers for this Dev, keep them coming 👍

[@paulhaywood9081]

Many thanks for the video. Really looking forward to the video on joining derived tables.

[@yanivsalman9685]

Great explanation

[@gotchihaeyo1825]

Thank you.

[@BeardedDevData]

No problem 👍

[@buksi771]

hey :)
i really love ur videos...currently, i'm taking a course about database development, basically, i'm learning it from 0 so from the most simple SELECT statement...i think my course is horrible, very hard to follow for someone like me who doesn't have any kind of IT knowledge whatsoever and ur videos are helping me a lot clear up the ppt-s i see in class.
near that it's hard to follow the presentations, we make very little practice so i have very little idea if i can use what i learn or not...could u pls recommend me some website (free or with a subscription) where i can practice sql/mssql? cuz like with Derived Tables, i understand the idea and logic of it but i have no idea if i could actually use/recognize them in an exercise.
thanks in advance.
Agnes

[@BeardedDevData]

Sure, the best website I can recommend is w3schools, it’s completely free and has its own editor for you to practice, www.w3schools.com/sql/

[@houstonvanhoy7767]

@@BeardedDevData Thank you for this tip and all the other stuff. ✔ Some of this is review, other parts are things which I didn't have time to grasp, other parts are new. It is all valuable.

[@houstonvanhoy7767]

Brad: This might be trivial. but I do have a question.
Some of the totals display more than two decimal places. Is that because you are calculating British Pounds instead of American Dollars? Thanks.

[@BeardedDevData]

Nothing to do with currency, as its the result of an expression it is returned with what is deemed a necessary precision, the result can be cast or converted.

[@20thCenturyFav]

what's the difference between this and a CTE (common table expression)

[@BeardedDevData]

Spoiler Alert ⚠️
I've got a video coming over the next couple of weeks comparing the two, however CTEs were mainly designed to address two common problems with derived tables. The first being readability, CTEs are defined at the top of a query and when nested can be more easily understood, the second is resuability, if I want to join to a derived table multiple times then I have to define it multiple times but with a CTE you only need to define it once. Hope that helps.