Can we use WITH for more than one nested query in SQL?
Yes, you can use WITH for more than one nested query. You can do so by listing each query using commas after the WITH.
For example,
WITH
query1 AS (SELECT column1 FROM table1 WHERE condition1),
query2 AS (SELECT column2 FROM table2 WHERE condition2)
…
How could I solve this problem?
Wouldn’t you just do this by comparing the price vs the average in a where clause?
select *
from table_name
where price_col > avg(price_col);
Hello guys. You can’t use AVG on a where clause. To do that you’ll have to do a subquery.
like:
select *
from table_name
where price_col > ( select avg(price_col) from table_name );
I still don’t understand why the return from avg is not possible on where but the return from a subquery is. Hope it helps anyway.
Does anyone know a good example on when you might use a WITH with more than 1 nested query?
Just trying to use that in the exercise where I have to separately calculate total points of all users, then total points of over-performers and divide one by another. Not sure if it’s a correct usage or if there is a better way though.
hey why this query cant be used in this given problem ,. can anyone help here?
SELECT customer_id,count(subscription_id) as ‘subscriptions’ from orders join customers on orders.customer_id= customers.customer_id ;
Thought it would be an interesting exercise to see if this problem can be solved using a join instead.
While the output order comes out slightly different, this query works:
SELECT customers.customer_name,
COUNT(subscription_id) AS ‘subscriptions’
FROM orders JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY customers.customer_name;
I also was interested in altering the code and came to the same result that even seems to be leaner and easier.
So, I wonder why using WITH at all while there are other more appealing options. Are there scenarios that can only be solved by using WITH?
As it was taught to us in Aggregate Function module we can not directly do comparison using WHERE while working on Aggregate Function instead we use HAVING clause. I hope this helps.
SELECT *
FROM table_name
GROUP BY column_name
HAVING price_col > AVG(price_col);
Thanks, I tested it and it works perfectly.
If you want the same result as the solution of the exercise, just change the last line to GROUP BY customers.customer_id:
SELECT customers.customer_name,
COUNT(subscription_id) AS 'subscriptions'
FROM orders
JOIN customers
ON orders.customer_id =
customers.customer_id
GROUP BY customers.customer_id;
Using WITH is definitely overkill in the example presented in the exercise but there are scenarios in which WITH is much friendlier than a bunch of crazy joins. It depends on how complex & clean the tables are and what you’re trying to do with the data. Maybe someone else can give a more specific example .
I believe it’s just for making code more readable in the join statements, if you have 5-10 temp subqueries used - this can be really hard to debug it later, with the WITH syntax you can make leaner join statements, which in case of complex relation will be hard to digest for someone reading the code.
Our BI team uses WITH frequently when you have for example 20 dictionaries to join along with several computational queries.
Though I personally get used to putting everything to join statements… but it’s sometimes really hard to read later day
While I reached here in my journey to learn SQL, I understand CodeAcademy provides a very basic level of SQL learning.
Is it because I am not a pro member here, can somebody suggest a better learning resource available online?
it can work if you modify it to:
SELECT customers.customer_name,COUNT(orders.subscription_id) AS ‘subscriptions’
FROM orders
JOIN customers
ON orders.customer_id= customers.customer_id
GROUP BY customers.customer_id ;
I used with in the first query, I used the result in the second query, and then I used the result of the second query in a third query. But it didn’t work. Is this incorrect in SQL?
WITH previous_query AS( SELECT customer_id,
COUNT(subscription_id) AS ‘subscriptions’
FROM orders
GROUP BY customer_id)
WITH second_query AS(SELECT customers.customer_name, previous_query.subscriptions
FROM customers
JOIN previous_query
ON customers.customer_id = previous_query.customer_id)
SELECT *
FROM second_query
JOIN customers;
+1, i have the same curiousity
I think we can’t use WITH twice in a SQL statement, and I’m not sure why did you want to add JOIN customers at the last line.
Just modify your statement a bit, then you can get what you want.
WITH previous_query AS(
SELECT customer_id,
COUNT(subscription_id) AS 'subscriptions'
FROM orders
GROUP BY customer_id),
second_query AS(
SELECT customers.customer_name,
previous_query.subscriptions
FROM customers
JOIN previous_query
ON customers.customer_id = previous_query.customer_id)
SELECT *
FROM second_query;
@course8869191296 @gafanhoto1990 @kingmalalma @java3346637497
@gafanhoto1990 's solution works well. I am not sure what you are proposing @course8869191296 works in this case as you are trying to get the average of the whole table, and not necessarily grouping by a group and you want the results at the individual record level.
An example using WITH statement, using the subscription data from the lesson, would be:
WITH average_price AS (
SELECT AVG(price_per_month) AS Average_Subscription_Price
FROM subscriptions
)
SELECT *
FROM subscriptions
CROSS JOIN average_price
WHERE price_per_month > average_price.Average_Subscription_Price;