0

This is my Query:

SELECT 
    COUNT(runs_scored) - SUM(IF(extra_type_id = 4, 1, 0)) AS Balls,
    SUM(runs_scored) + SUM(IF(extra_type_id IN (2 , 4),
        extra_runs,
        0)) AS Runs,
    SUM(IF(kind_out IN (1 , 2, 4, 6, 7, 8),
        1,
        0)) AS Wickets
FROM
    ball_by_ball BBB
        LEFT JOIN
    `database`.batsman_scored BS USING (match_id , over_id , ball_id , innings_no)
        LEFT JOIN
    `database`.extra_runs ER USING (match_id , over_id , ball_id , innings_no)
        LEFT JOIN
    `database`.wicket_taken WT USING (match_id , over_id , ball_id , innings_no)
WHERE
    bowler = 70
GROUP BY match_id
ORDER BY Wickets DESC , Runs;

This is Output:

+-------+------+---------+
| Balls | Runs | Wickets |
+-------+------+---------+
|    24 |   28 |       3 |
|    24 |   23 |       2 |
|    24 |   32 |       2 |
|    24 |   23 |       1 |
|    18 |   27 |       1 |
|    12 |   10 |       0 |
|    12 |   17 |       0 |
|     6 |   18 |       0 |
|    18 |   19 |       0 |
|    18 |   28 |       0 |
+-------+------+---------+

This is my Final Query:

SELECT 
    SUM(Balls) AS Balls,
    SUM(Runs) AS Runs,
    SUM(Wickets) AS Wickets
FROM
    (SELECT 
        COUNT(runs_scored) - SUM(IF(extra_type_id = 4, 1, 0)) AS Balls,
            SUM(runs_scored) + SUM(IF(extra_type_id IN (2 , 4), extra_runs, 0)) AS Runs,
            SUM(IF(kind_out IN (1 , 2, 4, 6, 7, 8), 1, 0)) AS Wickets
    FROM
        ball_by_ball BBB
    LEFT JOIN `database`.batsman_scored BS USING (match_id , over_id , ball_id , innings_no)
    LEFT JOIN `database`.extra_runs ER USING (match_id , over_id , ball_id , innings_no)
    LEFT JOIN `database`.wicket_taken WT USING (match_id , over_id , ball_id , innings_no)
    WHERE
        bowler = 70
    GROUP BY match_id
    ORDER BY Wickets DESC , Runs) AS A;

This is Output:

+-------+------+---------+
| Balls | Runs | Wickets |
+-------+------+---------+
|   180 |  225 |       9 |
+-------+------+---------+

But I want to get the Maximum Wickets and Runs like below:

+-------+------+---------+---------+
| Balls | Runs | Wickets | Max(W/R)|
+-------+------+---------+---------+
|   180 |  225 |       9 |    3/28 |
+-------+------+---------+---------+

Here 3 are Wickets and 28 are Runs which I got from Subquery first Row. Now how can i do that from my Final Query? I need to get Subquery first Row values which already sorted according to my requirement. I tried WINDOWS FUNCTION that not working.

  • Do you have a fiddle or your window functions attempt? – danblack Apr 15 at 1:14
  • CONCAT(MAX(Wickets), '/', FIRST_VALUE(Runs) OVER (ORDER BY Wickets DESC)) – Akina Apr 15 at 5:11
  • @danblack i tried what Akina suggested but that not work. to get FIRST_VALUE(Runs) i have to group by Runs which is not my solution. – Rhidoy Apr 15 at 6:41
  • @Akina i also tried that way but it's not working. To get FIRST_VALUE(Runs) i have to group by Runs which is not my solution. That is the error without group In aggregated query without GROUP BY, expression #4 of SELECT list contains nonaggregated column 'A.Runs'; this is incompatible with sql_mode=only_full_group_by – Rhidoy Apr 15 at 6:43
  • Who doesn't like debugging partial fragments of SQL and error messages to a query they can't see. – danblack Apr 15 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.