Sqlite (WITH statements)


#1

Hi there!

So I was working through the SQL training on here. And was requested to use a “WITH” statement. We are provided this original query:

SELECT customer_id, COUNT(subscription_id) as subscriptions
FROM orders
GROUP BY customer_id

So I did the task and ended up with the following to give me the customers names and number of subscriptions.

WITH previous_results as (
SELECT customer_id, COUNT(subscription_id) as subscriptions
FROM orders
GROUP BY customer_id )
SELECT c.customer_name, p.subscriptions
FROM previous_results p
JOIN customers c ON p.customer_id = c.customer_id;

However… my question to anyone reading this. Could you explain to me why/if we need a “WITH” statement for this. I wrote the following query and it worked just the same:

SELECT c.customer_name, COUNT(o.subscription_id) as subscriptions
FROM orders o JOIN customers c
ON o.customer_id = c.customer_id
GROUP BY o.customer_id;

If there isn’t any useful reason to use a “WITH” statement in this example, could you suggest an example of why this might be needed?

Thanks in advance!


#2

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.