-----------
--- CTE ---
-----------
--- Common table expression 


USE AdventureWorks2012
GO

with CTE_Emp
AS
(select * from HumanResources.Employee
 where Gender = 'F'
 and JobTitle not like 'Marketing%'
 and HireDate between '2002-03-03' and '2003-03-03')

select * from CTE_Emp

with CTE_Emp
AS
(select JobTitle, count(*) as cnt from HumanResources.Employee
 where Gender = 'F'
 and JobTitle not like 'Marketing%'
 and HireDate between '2002-03-03' and '2003-03-03'
 group by JobTitle
)

select * from CTE_Emp

Use Northwind

WITH products_and_categories 
	  (ProductName, CategoryName, UnitPrice) AS
(
	   SELECT
		  p.ProductName,
		  c.CategoryName,
		  p.UnitPrice
	   FROM Products p  JOIN Categories c ON
			 c.CategoryID = p.CategoryID
	   WHERE p.UnitPrice > 10.0
)
		SELECT ProductName, CategoryName, UnitPrice
		FROM products_and_categories;

Go

WITH products_and_categories
(ProductName, CategoryName, UnitPrice) AS
(
	   SELECT
		  p.ProductName,
		  c.CategoryName,
		  p.UnitPrice
	   FROM Products p  JOIN Categories c ON
			 c.CategoryID = p.CategoryID
	   WHERE p.UnitPrice > 10.0
)
		Select CategoryName, count(case when UnitPrice > 30 then 1 end) as cnt
		from
		(
		SELECT ProductName, CategoryName, UnitPrice
		FROM products_and_categories
		) pc
		group by CategoryName