To create CTE, use highlighted approach. Read below SQL statements carefully
WITH PersonalCTEName (TotalSalary, Name) AS ( SELECT SUM(NetSalary), LastName FROM viewPersonalAccounts WHERE LastName LIKE 'N%' GROUP BY LastName )
SELECT * FROM PersonalCTEName
UNION (
SELECT SUM(NetSalary), LastName FROM viewPersonalAccounts
WHERE NetSalary > 50000
GROUP BY LastName
) ORDER BY Name
The CTE definition starts with “WITH” keyword followed by the name of the CTE and then the column that this CTE will return. Under bracket, we write the SQL statements to return the data from this CTE (this SQL statements can be any valid SQL statements).
Here, the first highlighted part is the CTE defined whose data is being fetched from the following SELECT statement (SELECT * FROM PersonalCTEName) and we are using UNION clause to append the data from next query (get sum of records whose NetSalary is more than 50000 grouped by LastName) and then the combination of record from CTE and UNION block result is being sorted by Name.
Views: 14179 | Post Order: 107