CTE (Common Table Expression) Basics

WITH summary AS

(

SELECT vendor_state, vendor_name,

SUM(invoice_total) AS sum_of_invoices

FROM vendors v JOIN invoices i

ON v.vendor_id = i.vendor_id

GROUP BY vendor_state, vendor_name

)

The WITH and AS Keywords are used to define a CTE, once defined this “subroutine” can be called by other queries

You the programmer create the name for the CTE

This is the code that was formally in a subquery, it has been extracted and placed in this CTE