FAQ: Multiple Tables - With

Question about the AS function.

Does the AS function differ when combined as part of a WITH statement?

In a WITH function, it takes the format of:
WITH ‘Named Variable’ AS (SELECT query)…

but I can also use the AS function to name a column such as:
SELECT avg(Col1) AS ‘Named Variable’

Is there a reason why the naming of the variable occurs on different sides of the AS? Is it possible to switch both of these around? Such as:
WITH (SELECT query) AS ‘Named Variable’
SELECT ‘Named Variable’ AS avg(col1)

Are both of these formatted correctly, or does the side of the AS that does the naming vary specifically inside a WITH?

I feel like I missed something, I got the ON statement but used the wrong column names. I’m confused on how we were know to use customer_id?
Is it inferred by the previous_query?
or is that a common sense thing that just completely blew by me?

1 Like

I can’t use With with query that has join function inside
for example: with previous_table as(
select * from MONHOC
join KETQUA
on MONHOC.MaMonHoc = KETQUA.MaMonHoc
)
select * from HOCVIEN
join previous_table
on HOCVIEN.MaHocVien = previous_table.MaHV

It said “The column ‘MaMonHoc’ was specified multiple times for ‘previous_table’”

During this excise, I found that COUNT(subscription_id) was renamed AS ‘subscriptions’.
Then, it was called as previous_query.subscriptions
In my understanding, the AS syntax just provide a ‘nickname’ for the column, so I tried to not use the AS syntax, however, when I delete the AS sentence, the result returns nothing. Can someone tells me where am I wrong? Much Thanks!

My codes are like this:

WITH previous_query AS (

SELECT customer_id,

COUNT(subscription_id)

FROM orders

GROUP BY customer_id

)

SELECT customers.customer_name, previous_query.COUNT(subscription_id) FROM previous_query

JOIN customers

ON customers.customer_id = previous_query.customer_id;

This was helpful! I think I’d gotten so used to using AS after a statement to alias, that switching to WITH… AS before a SELECT statement was confusing me at first. This explanation helped a lot.

I am wondering the same thing! I had written everything just fine but when it came to the ON clause I got confused because I wasn’t sure what to put for it. At first I tried a CROSS JOIN because I didn’t know what to use for ON. But it said I was wrong and so I had to open up the hint. If it wasn’t for the hint I would have had no clue what to put for the ON clause. If anybody has any ideas could you please let us know?

Hey everyone.

I cant figure out what is the difference between my code and the solution for this excercise except for the lines and spacing everything else seems the same to me. Maybe a different perspective will be able to correct this for me.

with previous_query as(
select customer_id,
count(subscription_id) as ‘suscriptions’
from orders
group by customer_id)
select customers.customer_name,previous_query.subscriptions from previous_query
join customers
on customer.customer_id =
previous_query.customer_id;

Thanks in advance and help is much appreciated.

Found it - spelling mistake in ‘subscriptions’ and on ‘customers.customer_id’ instead of ‘customer.customer_id’

Can anybody tell me what is wrong with this code? Because I can’t see it…

WITH previous_query AS (
SELECT customer_id, COUNT(subscription_id) AS ‘subscriptions’
FROM orders
GROUP BY customer_id
)
SELECT customers.costumer_name, previous_query.subscriptions
FROM previous_query
JOIN customers
ON previous_query.costumer_id = customers.costumer_id;

I feel like this is not a good example of when to use WITH, because it can just be accomplished with a simple JOIN, as below… does anyone else agree with this?
SELECT
orders.customer_id,
customers.customer_name,
COUNT(orders.subscription_id) AS ‘subscriptions’
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id
GROUP BY orders.customer_id;

yes this is exactly my question, seems like the question is poorly prepared.

Original given code:

SELECT customer_id,
   COUNT(subscription_id) AS 'subscriptions'
FROM orders
GROUP BY customer_id;

My testing for understanding:

SELECT customer_id,
   COUNT(subscription_id) AS 'subscriptions'
FROM orders;

What is happening when counting without grouping by the customer id? Why is it outputting 3 for customer id. I understand why 20 is in the subscription’s column, since it is the sum of all the subscriptions in the table.

Why is WITH required in this example? Or is it just a style option. I think I solved it with inner join:

SELECT customer_name, COUNT(*) AS 'subscriptions'
FROM customers
JOIN orders
  ON customers.customer_id = orders.customer_id
GROUP BY customer_name;

Is with just an alternate way to solve this…? My guess is that it’s a way to solve it if we want to keep the tables separated, but I’m not sure.

The character you used for the single quote marks around “AS subscription” isn’t recognized as legit.
image

1 Like

Could someone help me to understand the final statement of this case?
I understand the idea of WITH.

But the problem is the final statement:

SELECT customers.customer_name, previous_query.subscriptions
FROM previous_query
JOIN customers ON previous_query.customer_id = customers.customer_id;

From where come those two columns: customers.customer_name, previous_query.subscriptions?
We took customer_id from orders!
This one previous_query.subscriptions is a total mysery for me.

Thankful for all help.