How to group multiple ROW as ARRAY

Question

This is my current Query:

SELECT innings_no,
Player_Name,
sum(NoBalls) as NB
 FROM (
 SELECT A.innings_no,
 A.bowler,
 A.Runs_In_Over + ifnull(B.Extra_runs, 0) = 0 as Maiden,
 A.Balls - ifnull(B.NoBalls, 0) as Balls,
 A.Runs_In_Over + ifnull(B.Extra_runs, 0) as Runs,
 ifnull(C.Wicket, 0) as Wickets,
 Zero,
 Four,
 Six,
 ifnull(Wides, 0) as Wides,
 ifnull(NoBalls, 0) as Noballs
 FROM (SELECT over_id,
 ABBB.innings_no,
 ABBB.bowler,
 count(ABBB.ball_id) as Balls,
 Sum(ABB.Runs_Scored) As 'Runs_In_Over',
 SUM(if(ABB.runs_scored = 0, 1, 0)) as Zero,
 SUM(if(ABB.runs_scored = 4, 1, 0)) as Four,
 SUM(if(ABB.runs_scored = 6, 1, 0)) as Six
 FROM `database`.ball_by_ball ABBB
 INNER JOIN `database`.batsman_scored ABB
 using (match_id, over_id, ball_id, innings_no)
 where match_id = 981018
 GROUP BY over_id, innings_no, bowler
 ) as A
 LEFT JOIN
 (SELECT BBBB.over_id,
 BBBB.innings_no,
 BBBB.bowler,
 SUM(if(BER.extra_type_id = 2, 1, 0)) as Wides,
 SUM(if(BER.extra_type_id = 4, 1, 0)) as NoBalls,
 Sum(BER.Extra_Runs) As 'Extra_runs'
 FROM `database`.ball_by_ball BBBB
 INNER JOIN `database`.extra_runs BER
 using (match_id, over_id, ball_id, innings_no)
 WHERE match_id = 981018
 and BER.Extra_Type_Id IN (2, 4)
 GROUP BY over_id, innings_no, bowler
 ) as B
 using (over_id, innings_no, bowler)
 LEFT JOIN
 (SELECT over_id,
 CBBB.innings_no,
 CBBB.bowler,
 count(CBBB.bowler) as Wicket
 FROM `database`.ball_by_ball CBBB
 INNER JOIN `database`.wicket_taken CWT
 using (match_id, over_id, ball_id, innings_no)
 WHERE match_id = 981018
 and CWT.kind_out in (1, 2, 4, 6, 7, 8)
 GROUP BY over_id, innings_no, bowler
 ) as C
 using (over_id, innings_no, bowler)
 order by innings_no, over_id
 limit 1000
 ) AS DATA
 INNER JOIN `database`.Player C
 ON DATA.bowler = C.Player_Id
 GROUP BY innings_no, Player_Name;

This is Output:

+------------+---------------+------+
| innings_no | Player_Name | NB |
+------------+---------------+------+
| 1 | S Aravind | 0 |
| 1 | Iqbal Abdulla | 0 |
| 1 | SR Watson | 0 |
| 1 | CJ Jordan | 0 |
| 1 | YS Chahal | 0 |
| 1 | STR Binny | 0 |
| 2 | P Kumar | 0 |
| 2 | DS Kulkarni | 0 |
| 2 | RA Jadeja | 0 |
| 2 | SB Jakati | 1 |
| 2 | DJ Bravo | 0 |
| 2 | DR Smith | 0 |
+------------+---------------+------+

I want to get output as below:

+------------+--------------------------------+------------+
| innings_no | Player_Name | NB |
+------------+--------------------------------+------------+
| 1 | ["S Aravind", .. ,"STR Binny"] | [0, .. ,0] |
| 2 | ["P Kumar", .. ,"DR Smith"] | [0, .. ,0] |
+------------+--------------------------------+------------+

In the above output, I want to group my result by innings_no and convert those two rows to an array. I tried JSON_ARRAYAGG() function on Player_Name but the output is not what I want, also it does not work on sum(NoBalls).

How can I solve this?

Answer 1

WITH cte AS (`your query text except last semicolon`)
SELECT innings_no,
 JSON_ARRAYAGG(Player_Name) Player_Names,
 JSON_ARRAYAGG(NB) NBs
FROM cte
GROUP BY innings_no;

If You need a strict position according Player_Name and NB, use

WITH cte AS (`your query text except last semicolon`)
SELECT DISTINCT innings_no,
 JSON_ARRAYAGG(Player_Name) OVER win Player_Names,
 JSON_ARRAYAGG(NB) OVER win NBs
FROM cte
WINDOW win AS (PARTITION BY innings_no ORDER BY Player_Name);

• Rhidoy: i can do that by make my Query as a sub query. so can you please tell me is there any performance issues over using CTE?
• Akina: I think no difference between CTE and subquery, because only one copy of CTE table is used. To be sure you can compare execution plans. And I recommend you to use CTEs instead of subqueries (and in your query too) - the query text will be more readable and clear.
• Rhidoy: sorry for my lack of knowledge on database. execution plan means EXPLAIN? there both result is same. well in future i will use CTE, but with this problem, is there no easy way to solve this without using CTE or subqueries
• Akina: @Rhidoy execution plan means EXPLAIN? Yes. there both result is same I expected just such a result.
• Rhidoy: well in future i will use CTE, but with this problem, is there no easy way to solve this without using CTE or subqueries. or this is the efficient solution?

Answer 2

You need to GROUP BY only innings_no otherwise each unique innings_no,player_name is a unique entry.

To get the aggregate player_names:

CONCAT('[', GROUP_CONCAT(CONCAT('"', player_name, '"'), ']') as Player_Names

And NBs

CONCAT('[', GROUP_CONCAT(NoBalls), ']') as NBs

• Rhidoy: CONCAT('[', GROUP_CONCAT(CONCAT('"', player_name, '"'), ']')) as Player_Names gives the same output as JSON_ARRAYAGG() group by innings_no. Output is below: ["STR Binny"],"CJ Jordan"],"Iqbal Abdulla"],"CJ Jordan"],"S Aravind"],"CJ Jordan"],"S Aravind"],"SR Watson"],"CJ Jordan"],"YS Chahal"],"YS Chahal"],"YS Chahal"],"SR Watson"],"SR Watson"],"SR Watson"],"Iqbal Abdulla"],"YS Chahal"],"S Aravind"],"Iqbal Abdulla"],"Iqbal Abdulla"]