Suraz loves to watch cricket, during the recent IND vs SA T20 match Suraz was traveling so unfortunately he missed the match. But later the next day, he has watched the highlights after watching the highlights he is confused and he wants to understand in which Over how many runs are scored by team India. He has a table called Cricket in which he is having two entities one is ball number and the second is the runs scored over that ball.
Problem Statement:-
Write a SQL Query to help Suraz to calculate in which Over how many runs are scored by Team INDIA.
Sample INPUT:-
Sample Output:-
My Solution:-
In Order to Calculate the Over I have used the CASE WHEN Statement(ball_no%6=1) this will generate the same no for 1-6,6-12, 13-18, and so on. As I want this value to present with each row so I have used the Over Clause with Order BY ball_no.
After this, I have calculated the total runs scored in an Over by using the GROUP BY Clause.
Final Query:-
WITH CTE AS(
SELECT * ,SUM(CASE WHEN ball_no%6=1 THEN 1 ELSE 0 END) OVER(ORDER BY ball_no) AS Overs
FROM cricket
)
SELECT Overs, SUM(run) AS Runs FROM CTE GROUP BY Overs;
Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.
