Multivalue Correlated Subqueries Part 2

Video Transcription

Welcome back everyone. Let’s continue our discussion on correlated sub queries. So previously, we showed some different examples on how we can rewrite different sub queries with different operations like left join, we compared the differences between or the results of the in operator in the exists operator. But let’s take a look at a deeper example. Or a larger example of a correlated sub query, or at least another common use. It’s one of the common uses that I listed, we’re running totals and for a variety of calculations like that. So this is one variation of that use case. So most of the most of this query is something that we actually have done before, it appears more complicated than it really is. So let’s first start out by explaining this query using the from clause since that’s where we initially start out with our processing. So from sales orders, enter, join on sales order lines, where order date is between in 2015. And then we group group by order ID and dates, order by the dates, and percentage daily total. The columns that we pull out here are order date, order, order ID, the order total, which we have calculated before in a previous video. But the big addition here is this sub query here.

So this is the new part of the query. So what I’m calculating here is the percentage of sales that this order contributes to for that for a particular date. So for the date that this order actually happened on how much money of the of that total, total tally, did it contribute? So 100 times some, so 100 times the order price divided by So some and I use RT as an abbreviation for return. So some of the return order lines quantity, so this is the, again, calculating the order total, from sales orders return Oh, so our to enter join order lines, again, different alias return order lines. And so again, this is still within our inner sub query, our our sub query here, but here is the our link to the outside our to return order date equals ODOT, order date, so give me so what this sub query is doing is calculating the total amount of sales that happened on this particular order date. So for this particular date, what was the total amount made, and we use that to divide into the order, the order total for that one single order, and we get that percentage. So again, a little bit, it looks a lot more complicated than it actually is most this we’ve seen before. And again, really, this inner query is calculating the total, the total amount made for that particular day from the outer query, and then using that in the outer query to calculate the percent of the daily total.

So let’s do let’s review one more example like this. So again, a running total is a another general use case for a correlated sub query. So let’s paste this one in here. Another example of how a correlated sub query can be used. So this time we have up let’s go and run this again. This time, we have a lot of the customer information being pulled in here, but we have order date order total, and then you’re YTD year to date running total for customer. This one is a little bit more complicated than what we had before. So we have a two joins here, customers on orders, orders on order lines, we’ve done this join before. So we can link the customers customer to the order that they made, grouped by the customer and then grouped by the customer first, then grouped by the order. So all the order for that particular customer are grouped together for each order. And then we select the customer information, order order dates, the order total.

So that’s all something that we We’ve covered before, but here is our new addition, our correlated sub query. So for this correlated sub query, we’re calculating the order total. From orders, inner join on order lines, where the customer ID from the inner table, right, the inner R T, O, the inner order table matches the customer ID of the customer table in the outer query. And the order ID matches the order ID in the outer query and the yours match yours match. And so if we assume that the order IDs are consecutive, that means the total running here is true, right year to date running total for the customer. So this is the first order that customer one place this is the total sum of 690 plus 3636, which gives us 3756, and so on, and so forth. So this is a really neat way to do a running total, just like what you would potentially do in like an Excel document. But we’re achieving this all with SQL with a correlated sub query. So this is probably one of the more common complicated uses of a correlated sub query. But again, as we showed earlier, the simpler use uses of a correlated sub query. Most of the time can be rewritten using just joints. But that will conclude all of the examples that I’ll talk about in videos for sub queries. If you do have questions, please reach out and we’ll be happy to help