Often times, we want to combine two tables, but one of the tables is the result of another calculation.
Let’s return to our magazine order example. Our marketing department might want to know a bit more about our customers. For instance, they might want to know how many magazines each customer subscribes to. We can easily calculate this using our
SELECT customer_id, COUNT(subscription_id) AS 'subscriptions' FROM orders GROUP BY customer_id;
This query is good, but a
customer_id isn’t terribly useful for our marketing department, they probably want to know the customer’s name.
We want to be able to join the results of this query with our
customers table, which will tell us the name of each customer. We can do this by using a
WITH previous_results AS ( SELECT ... ... ... ... ) SELECT * FROM previous_results JOIN customers ON _____ = _____;
WITHstatement allows us to perform a separate query (such as aggregating customer’s subscriptions)
previous_resultsis the alias that we will use to reference any columns from the query inside of the
- We can then go on to do whatever we want with this temporary table (such as join the temporary table with another table)
Essentially, we are putting a whole first query inside the parentheses
() and giving it a name. After that, we can use this name as if it’s a table and write a new query using the first query.
Place the whole query below into a
WITH statement, inside parentheses
(), and give it name
SELECT customer_id, COUNT(subscription_id) AS 'subscriptions' FROM orders GROUP BY customer_id
Join the temporary table
customers table and select the following columns:
Check the answer in the hint below.