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?
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?
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;
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;
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.
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.