 Hello friends, welcome to SSUnitech. So, see this side and this is continuation of SQL Server interview questions and answers. So, today we are going to discuss one more question. So, here we want to aggregate based on multiple tables. So, what it means? Let's move to another slide where we will understand about the question. So, here as you can see we have two input tables. First is related to student fees and second is related to student charges. In our student fees we can see we have only two columns and in charges again we have only two columns. It's possible that any student can contain only fees and contains only in charges and it may contains both. So, in our output we want to display all the records like we can see in our fee table we don't have student ID 3 but in our output we are getting 3. In our charges table we don't have ID 4 but in our output we are getting 4. If the charges is not available for that student then that column value will contain 0 in our output and if the fee is not available in our input table for that student then that would be 0 in our output. So, go to on SSMS to write the query. So, here I have already typed the query to create the table and insert record on it. So, I am going to execute these queries to create the table. So, tables have been created successfully. Now, I am going to insert record on it. So, we can execute our insert statement as we can see records have been inserted successfully on these two tables. Now, we can execute our selected statement to check the records. So, as you can see we are getting the same number of records as we have displayed in our slide. So, now I am going to copy these records and go to on Excel to understand the logic. So, this is for fee and second we need to copy for charges as well. So, we can copy, we can paste in Excel. So, as we can see here we have fee and here we have charges and in our output we want student ID then fee then charges. So, we want these three columns in our output. So, we can copy this and paste in our output and in our charges we are getting zero and when we talk about charges then we can copy our charges and we can paste it here and this is for charges. So, we can cut these values and paste in our charges and this time fee amount would be zero. Now, if we can apply aggregate with this output then we will get our actual output. As we can see student ID 1 then we will talk about the charges then we have these two values in our charges. So, our output fee would be 300. When we look in our charges then we can see we have all these three values so output would be 500. So, in our charges we will see 500. Next our student ID 2 then we can see output would be 400 for fee. So, we can write 400 and then when we look in charges then we can see we are getting 600. So, charges would be 600. Then we look in ID 3. So, here we can see in our ID 3 once we talk about fee then fee values are zero. So, fee would be zero. Then we need to look in charges. So, here we can see 850. So, this is our logic by which we can get our output. So, go to on SSMS to write the query by which we can get the output. So, first I want to select a stick from student fee table. So, here we can see we are getting student ID fee and zero value for the charges. So, this is our charges. Now, we can execute this lecture statement then we are getting the output. Now, I want a union with the charges table as well. So, this is for union all. So, here we can execute then we can see we are getting student ID and charges. So, we need to write the column names. So, this is for student ID then we have our second column with the fee. So, fee value is zero for the charges table and then we need to write the charges. So, this is our charges. Now, I am going to execute this lecture statement. So, as we can see we are getting the intermediate output as we have seen in our Excel. Now, we can use our derived table. So, this is our derived table and I can put an alias with a and here I want to select student ID then we have fee. So, we can write fee then we have charges. So, we can write charges but here we want to use aggregate function with some as fee then here we can write some for the charges. So, this is our charges and we are getting these values from this derived table as we have used aggregate function. So, we need to specify our group by with a student ID. Now, I want to execute this query. So, as we can see we are getting one two three and four all the student IDs. So, by using this query simply we will get our output. So, I hope you have understand how we can write the code to get the output. If you need this script it will be available on my Facebook page you can copy from there. If you like this video, please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.