SQL Aggregate function and WITH statement

Hi :slight_smile:

I’ve been trying out some code (not particularly any exercise) at the end of the Learn SQL and I was wondering why this comes up perfectly with results:

WITH previous_query AS (

SELECT customer_id,

  COUNT(subscription_id) AS 'subscriptions'

FROM orders

GROUP BY customer_id)

SELECT customers.customer_name,

previous_query.subscriptions

FROM previous_query

JOIN customers

ON previous_query.customer_id = customers.customer_id;

But this does not:

WITH previous_query AS (

SELECT customer_id,

  COUNT(subscription_id) AS 'subscriptions'

FROM orders

GROUP BY customer_id)

SELECT customers.customer_name,

previous_query.subscriptions

FROM previous_query;

I’m guessing it has something to do with the fact that the first query joins the ‘customers’ table with the previous query table but I’m not sure then how it selects ‘customers.customer_name’ FROM previous_query? or am I interpreting this wrong?

Thanks in advance! I know the second query is wrong but I can’t explain why so I was hoping someone here could :slight_smile:

You get access to the columns of the table you are joining, which is why selecting customers.customer_name worked in your first query, since you were selecting from previous_query and joining with customers

Your second query can’t find that column because it isn’t joining the table with access to it.

Your line of thinking is on the right track!

2 Likes

That makes it much clearer :slight_smile: thanks for the quick reply!

1 Like