 Hello, welcome to SSUnited, so see this site and this is continuation of SQL Server interview questions results. So today we are going to discuss one more interview question that was asked in Pine Labs. So one of my friend has attended interview with the Pine Lab. So this question was asked over there. So let's move to next slide to understand about the question. So here as we can see we are having input table that would have five columns. First is players then match then over then run and then wickets. So in the output we are also having five columns with the player name then total number of matches then total over then total runs then total wickets. So in case of Karthik he played three matches with the M1, M2 and M3. So in the total number of matches it is going to be done as three. In the over he bold 2.2 overs in the first match 3.0 over in the second match and 3.3 over in the third match. So total number of overs would be 8.5 in case of run 14, 22 and 12. So in the output total number of runs is 48. He has taken two wickets in the first match then one wicket then four wicket. So total number of wickets would be 7. So this is the output. So we can directly use the count function to get the total number of matches and we can use the sum function to directly get the run and total number of wickets. But in case of over we can't use the sum function directly because in a single over it has six balls. If we get six after the decimal places then that would be added with one over. So how we can write the query for getting this output. So let's move to SQL Server Management Studio where we will write the query for getting the output. As I am using SQL Server 2017 you may have another version of SQL Server. So first of all I would like to execute the create table statement to create the table. So command completed successfully. So table is created. Now I just want to insert record on the table so we can execute this insert statement. So nine rows affected. So data is successfully inserted into the table. And we can check. So here we are having the same metadata as we seen in the slide. First of all I would like to discuss about the parse name function. So parse name function is going to use get the values after the decimal places. So how we can use and we can check. So parse name and it is except two parameters first with the column name. So we want to get the column name from over and how many decimal places we want. So we just want only one decimal place. Now we can execute and check the output. So here as we can see 2.2 so we are getting 2 here 3.0 so we are getting 0 3.3 so 3. So we are getting the values only after decimal place. What would be the logic? So logic is very simple. First I need to get the only numeric part from this over and then this decimal part from the over. In the numeric part that would be like 2 words so we need to multiply by 6 because in a single over that would have 6 balls. So that would be total number of balls. So in case of Karthik match 1 we will get 12 plus 2 so 14. Then 3 so that would be 18. In 3.3 so we need to multiply 3 with 6 plus 3 that would be 21. Then we need to combine all the balls and then we need to divide by 6 and if we will get anything after the decimal places then we need to use that one as well. So let me write the query for the better understanding. So here I just want to get the player name first. So this is our player name. Second I just want to use count with the total number of match. So we can use count with the matches and it would be total number of matches. After that we can use our logic. So first of all I would like to convert this into integer to get the integer part over as integer. Now it looks good. Then we need to multiply it by 6. After that we can use the sum and here we need to use the pass name function as we seen. Then over how many different places we want? We want only one decimal place and we can combine all the balls. So we can use the sum function with this and let me call this as total balls. Then we need to use run. So we can use the sum function for the run and directly we can use as run looks good. Then we need to combine total number of wickets. So again we can use the sum of wickets and let me call this as wicket. Now as I am going to use the aggregate function so we have to use group by clause. So group by with the player name. Now let me execute and check the output. So here as we can see we are having total number of balls, total number of runs, total wickets and total matches. So let me compare with the output. So here as we can see in case of Deepak he bought 7.0, 36 runs and 5 wickets. So here we can see 36 runs, 5 wickets and 3 matches and total balls 42. So now we need to convert this total balls into overs. So let me put this into a CT. So this is the CT and now we can use this select from CT. Now I need to get the total overs. So for that we can use total balls divided by 6. Now we can execute. So this time we will get but if we will have anything after decimal place that would be missing. So 7, 7 and 8. Now I need to cast this value as worker 10. Then I just want to combine dot that is fine. Then we have to get values after the decimal places. So how we can get? So we can directly use total balls. Then we can use mod with 6 and we need to also convert this as worker. So let me convert this as worker and 10. Now let me execute and check the outputs. So this would be the total number of overs. So Karthik has bold 8.5 overs. Now we can check Karthik has bold 8.5 overs, Deepak 7.0 and Divyaan 7.2. So we are getting the output as we were expecting. So thank you so much for watching this video. 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. And if you need this script it will be available on the description of this video. So you can copy from there. Thank you so much for watching.