Microsoft SQL CTE Common Table Expressions

Microsoft SQL CTE Common Table Expressions

-- CTE results in a temporary table expression
-- CTE can use Select, Insert, Update, and Delete statements
-- CTE is similar to subqueries
-- More than one CTE can be used together by comma separating the CTE queries
-- CTE always starts with the WITH statement

with fxmajors (product, productCounterTotal)
as
(select product, count(*) as productCounterTotal
from [test].[dbo].[options on fx majorsii]
group by product
having count(product)>2)
select *
from fxmajors, [test].[dbo].[options on fx majorsii]
where fxmajors.product=[test].[dbo].[options on fx majorsii].Product;