Derived Columns with CTEs

Derived Columns with CTEs.


Let's say we have a simple query selecting few columns from some table. Now we are asked do some derived columns from this queries. However, some of these derived columns
are based another derived columns.

One way to do this is to create a view and get a first set of derived columns and than create another view on the first view to derive another sets of columns.

However there is another approach to do this. Take the initial query results and put into a common table expression, use another common table expression to get a first derived columns and
use it derive next set of columns. Theoretically this can be done n-times but to illustrate this, let's try to limit this to 3 CTEs.



This is simple table I have created to demonstrate derived columns from CTEs. Not a perfect but just to illustrate the concept.



Create table Student_Grade
(ID int IDENTITY,
FirstName varchar(50),
MiddleName varchar(10),
LastName varchar(50), 
Maths float,
Biology float,
Chemistry float,
Physics float,
English float,
ForeignLanguage float,
Physical_Education float
)

--Let's enter some data in our sample tables.

Insert into Student_Grade
VALUES
('Abby', 'M', 'Smith',40,67,38,25,67,71,34),
('Bbby', 'M', 'Smith',50,77,48,35,57,72,44),
('Cbby', 'M', 'Smith',68,87,58,45,67,73,54),
('Dbby', 'M', 'Smith',78,97,68,55,27,74,64),
('Ebby', 'M', 'Smith',88,57,78,65,17,75,74)

--Let's see if we have our data in the table or not.

Select * from Student_Grade;



-- Let's say we are asked to find what student made in total_science_score, combined average in Science subjects (Biology, physics, and Chemistry) and English and ForeignLanguage.

With FirstSelect AS
(
SELECT  [ID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Maths]
      ,[Biology]
      ,[Chemistry]
      ,[Physics]
      ,[English]
      ,[ForeignLanguage]
      ,[Physical_Education]


  FROM [dbo].[Student_Grade]
  ),

  --Here for simple purpose I am deriving two columns -Total_Science_Score and Total_language_Score
  SecondSelect AS
  ( Select [ID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
 ,[Maths]
      ,[Biology]
      ,[Chemistry]
      ,[Physics]
      ,[English]
      ,[ForeignLanguage]
      ,[Physical_Education]
 ,[Biology]+[Chemistry]+ [Physics] As Total_Science_Score
  ,[English]+[ForeignLanguage] As Total_language_Score

 From FirstSelect
 ),

--Here I am deriving another columns which was derived in previous statement

ThirdSelect AS
(
 Select
 [ID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
 ,[Maths]
      ,[Biology]
      ,[Chemistry]
      ,[Physics]
      ,[English]
      ,[ForeignLanguage]
      ,[Physical_Education]
 ,Total_Science_Score
 ,Total_language_Score
 , Total_Science_Score + Total_language_Score AS Total_Science_Language_Score
  from SecondSelect
 )

 Select * from ThirdSelect