For each in mysql query

[Adamhumbug]

Hi All,

I have a query that selects people from my DB

 

SELECT player.first_name, player.last_name, player.id
FROM game_squad
	inner join player on game_squad.player_id = player.id
	inner join game on 51 = game.id
	where game_id = 51 and team_id = game.batting_first

The ids that i get in this query, i need to use to do some sums on other data.

I amended the suery to be

SELECT player.first_name, player.last_name, player.id, (Select sum(deliveries.runs_scored) from deliveries where deliveries.on_strike_batter_id = 20) 
FROM game_squad
	inner join player on game_squad.player_id = player.id
	inner join game on 51 = game.id
	where game_id = 51 and team_id = game.batting_first

the sub query value that is currently 20, needs to be the ID per row that has been selected from the first part of the query.

Is this possible?

 

If my explanation is not clear enough or more info required around table structure, i will be happy to provide.

Edited November 6, 2022 by Adamhumbug

[Barand]

Yes, it can be done, but using a dependent subquery like that is the slowest way to do it.

Can you provide a dump of the relevant tables' data and give us some idea of how you want the final output to appear?

[Adamhumbug]

Thanks for this Barand.

CREATE TABLE `game_squad` (
  `id` int(11) NOT NULL,
  `game_id` int(11) NOT NULL,
  `player_id` int(11) NOT NULL,
  `wicket_keeper` tinyint(1) NOT NULL,
  `captain` tinyint(1) NOT NULL,
  `batting_number` tinyint(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `game_squad`
--

INSERT INTO `game_squad` (`id`, `game_id`, `player_id`, `wicket_keeper`, `captain`, `batting_number`) VALUES
(14, 51, 1, 0, 0, 0),
(15, 51, 6, 0, 0, 0),
(16, 51, 3, 0, 0, 0),
(17, 51, 11, 0, 0, 0),
(18, 51, 2, 0, 0, 0),
(19, 51, 7, 0, 0, 0),
(20, 51, 5, 0, 0, 0),
(21, 51, 10, 0, 0, 0),
(22, 51, 9, 0, 0, 0),
(23, 51, 12, 0, 0, 0),
(24, 51, 8, 0, 0, 0),
(25, 51, 20, 0, 0, 0),
(26, 51, 21, 0, 0, 0),
(27, 51, 17, 0, 0, 0),
(28, 51, 16, 0, 0, 0),
(29, 51, 14, 0, 0, 0),
(30, 51, 18, 0, 0, 0),
(31, 51, 13, 0, 0, 0),
(32, 51, 15, 0, 0, 0),
(33, 51, 19, 0, 0, 0),
(34, 51, 23, 0, 0, 0),
(35, 51, 22, 0, 0, 0),
(36, 52, 20, 0, 0, 0),
(37, 52, 21, 0, 0, 0),
(38, 52, 17, 0, 0, 0),
(39, 53, 20, 0, 0, 0),
(40, 53, 21, 0, 0, 0),
(41, 53, 17, 0, 0, 0),
(42, 56, 1, 0, 0, 0),
(43, 56, 6, 0, 0, 0),
(44, 56, 3, 0, 0, 0),
(45, 56, 20, 0, 0, 0),
(46, 56, 21, 0, 0, 0),
(47, 56, 17, 0, 0, 0);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `game_squad`
--
ALTER TABLE `game_squad`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `game_squad`
--
ALTER TABLE `game_squad`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=48;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

CREATE TABLE `game` (
  `id` int(11) NOT NULL,
  `home_team` int(11) NOT NULL,
  `away_team` int(11) NOT NULL,
  `date` date NOT NULL,
  `result_id` int(11) DEFAULT NULL,
  `venue` text NOT NULL,
  `toss_won_by` int(11) DEFAULT NULL,
  `batting_first` int(11) DEFAULT NULL,
  `bowling_first` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `game`
--

INSERT INTO `game` (`id`, `home_team`, `away_team`, `date`, `result_id`, `venue`, `toss_won_by`, `batting_first`, `bowling_first`) VALUES
(51, 1, 2, '2022-02-01', NULL, 'Avenue Park', 1, 2, 1),
(55, 1, 2, '2022-11-06', NULL, 'TEST', 2, 1, 2),
(56, 1, 2, '2022-11-06', NULL, 'adam', NULL, NULL, 0);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `game`
--
ALTER TABLE `game`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `game`
--
ALTER TABLE `game`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=57;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

 

CREATE TABLE `player` (
  `id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `player_type_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `player`
--

INSERT INTO `player` (`id`, `team_id`, `first_name`, `last_name`, `player_type_id`) VALUES
(1, 1, 'Adam', 'Hewitt', 4),
(2, 1, 'Jack', 'Farmer', 4),
(3, 1, 'Chuggy', 'Jones', 4),
(4, 1, 'Sean', 'Baines', 3),
(5, 1, 'James', 'Rist', 1),
(6, 1, 'Brett', 'Lee', 1),
(7, 1, 'James', 'Anderson', 1),
(8, 1, 'Sean', 'Smith', 2),
(9, 1, 'Ricky', 'Ponting', 2),
(10, 1, 'Justin', 'Langer', 2),
(11, 1, 'Ian', 'Bell', 2),
(12, 1, 'Robert', 'Key', 2),
(13, 2, 'John', 'Smith', 2),
(14, 2, 'James ', 'Johnson', 3),
(15, 2, 'Lawrence', 'Stroll', 4),
(16, 2, 'Ian', 'Chapell', 2),
(17, 2, 'Graham', 'Gooch', 2),
(18, 2, 'Jaques', 'Kallis', 1),
(19, 2, 'Maddison', 'Muttiah', 2),
(20, 2, 'Brian', 'Lara', 1),
(21, 2, 'Freddie', 'Flintoff', 4),
(22, 2, 'Simon', 'Jones', 4),
(23, 2, 'Richard', 'Bull', 4);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `player`
--
ALTER TABLE `player`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `player`
--
ALTER TABLE `player`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

CREATE TABLE `deliveries` (
  `id` int(11) NOT NULL,
  `game_id` int(11) NOT NULL,
  `on_strike_batter_id` int(11) NOT NULL,
  `runs_scored` int(11) NOT NULL,
  `fair_delivery` tinyint(1) NOT NULL,
  `over_number` int(11) NOT NULL,
  `ball_in_over` int(11) NOT NULL,
  `over_called_this_ball` tinyint(1) NOT NULL,
  `wicket` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `deliveries`
--

INSERT INTO `deliveries` (`id`, `game_id`, `on_strike_batter_id`, `runs_scored`, `fair_delivery`, `over_number`, `ball_in_over`, `over_called_this_ball`, `wicket`) VALUES
(126, 51, 21, 1, 1, 1, 1, 0, 0),
(127, 51, 20, 1, 1, 1, 1, 0, 0),
(128, 51, 21, 1, 1, 1, 1, 0, 0),
(129, 51, 20, 2, 1, 1, 1, 0, 0),
(130, 51, 20, 2, 1, 1, 1, 0, 0),
(131, 51, 20, 2, 1, 1, 1, 0, 0),
(132, 51, 20, 2, 1, 1, 1, 0, 0),
(133, 51, 20, 3, 1, 1, 1, 0, 0),
(134, 51, 21, 3, 1, 1, 1, 0, 0),
(135, 51, 20, 1, 1, 1, 1, 0, 0),
(136, 51, 21, 1, 1, 1, 1, 0, 0),
(137, 51, 20, 3, 1, 1, 1, 0, 0),
(138, 51, 21, 3, 1, 1, 1, 0, 0),
(139, 51, 20, 6, 1, 1, 1, 0, 0),
(140, 51, 20, 6, 1, 1, 1, 0, 0),
(141, 51, 20, 1, 1, 1, 1, 0, 0),
(142, 51, 21, 6, 1, 1, 1, 0, 0),
(143, 51, 21, 6, 1, 1, 1, 0, 0),
(144, 51, 21, 4, 1, 1, 1, 0, 0),
(145, 51, 21, 4, 1, 1, 1, 0, 0),
(146, 51, 21, 1, 1, 1, 1, 0, 0),
(147, 51, 20, 4, 1, 1, 1, 0, 0),
(148, 51, 20, 4, 1, 1, 1, 0, 0),
(149, 51, 20, 4, 1, 1, 1, 0, 0),
(150, 51, 20, 4, 1, 1, 1, 0, 0),
(151, 51, 20, 4, 1, 1, 1, 0, 0),
(152, 51, 20, 4, 1, 1, 1, 0, 0),
(153, 51, 20, 4, 1, 1, 1, 0, 0),
(154, 51, 20, 1, 1, 1, 1, 0, 0),
(155, 51, 21, 4, 1, 1, 1, 0, 0),
(156, 51, 21, 4, 1, 1, 1, 0, 0),
(157, 51, 21, 4, 1, 1, 1, 0, 0),
(158, 51, 21, 4, 1, 1, 1, 0, 0);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `deliveries`
--
ALTER TABLE `deliveries`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `deliveries`
--
ALTER TABLE `deliveries`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=159;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

What i am wanting to get out of this as a structure is the following:

First Name     Last Name     ID     SumOfRunsScored

 

I will be adding more to this such as number of 4's scored, number of 6's scored, number of deliveries faced, strike rate etc but happy to do that work myself following your assistance.

This is for a cricket scoring app if that gives you any greater insight.

Let me know if this doesnt give you what you need.

 

Thanks Again

Edited November 6, 2022 by Adamhumbug

[Barand]

Your data model is confusing me regards the (lack of) relationship between player/squad/team. It seems as though there should be one, but I can't see it.

[Adamhumbug]

apologies, i do have this.

 

CREATE TABLE `team` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `team`
--

INSERT INTO `team` (`id`, `name`) VALUES
(1, 'Town CC 1XI'),
(2, 'Other Test CC 1XI');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `team`
--
ALTER TABLE `team`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `team`
--
ALTER TABLE `team`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

Edited November 6, 2022 by Adamhumbug

[Barand]

Thanks. That puts the players into team 1 or team 2. How does "squad" fit into the picture?

What, in your model, is a squad?

[Adamhumbug]

I am assuming that it is possible to have 100 or more players in a team.  But the game can only have 11 per side.  The squad is the 11 players that have been picked per team to play for that team in that game.

Edited November 6, 2022 by Adamhumbug

[Barand]

Does this come close to what you were after?

SELECT p.first_name
     , p.last_name
     , p.id as playerid
     , sum(runs_scored) as runs
FROM game g
     JOIN player p ON g.batting_first = p.team_id
     JOIN deliveries d ON p.id = d.on_strike_batter_id
                       AND g.id = d.game_id
WHERE g.id = 51
GROUP BY p.id ;
+------------+-----------+----------+------+
| first_name | last_name | playerid | runs |
+------------+-----------+----------+------+
| Brian      | Lara      |       20 |   58 |
| Freddie    | Flintoff  |       21 |   46 |
+------------+-----------+----------+------+

 

7 minutes ago, Adamhumbug said:

I am assuming that it is possible to have 100 or more players in a team.  But the game can only have 11 per side.  The squad is the 11 players that have been picked per team to play for that team in that game.

That is what I thought but what connects a squad to team?

EDIT:

I would have expected the squad to have a key (team_id, game_id) to show is was the team squad for that game.

[Adamhumbug]

30 minutes ago, Barand said:

Does this come close to what you were after?

SELECT p.first_name
     , p.last_name
     , p.id as playerid
     , sum(runs_scored) as runs
FROM game g
     JOIN player p ON g.batting_first = p.team_id
     JOIN deliveries d ON p.id = d.on_strike_batter_id
                       AND g.id = d.game_id
WHERE g.id = 51
GROUP BY p.id ;
+------------+-----------+----------+------+
| first_name | last_name | playerid | runs |
+------------+-----------+----------+------+
| Brian      | Lara      |       20 |   58 |
| Freddie    | Flintoff  |       21 |   46 |
+------------+-----------+----------+------+

 

That is what I thought but what connects a squad to team?

EDIT:

I would have expected the squad to have a key (team_id, game_id) to show is was the team squad for that game.

Absolutely perfect - as always, thank you very much!

[Adamhumbug]

Just as an extension to this.

I am also wanting to count how many times each player has scored 4 runs.

I thought the following sub qry might do it but alas it is giving me the same number for each player.

SELECT p.first_name
	, p.last_name
	, p.id as playerid
	, sum(runs_scored) as runs
	, count(runs_scored) as balls
	, (Select count(runs_scored) from deliveries where runs_scored = 4) as fours
FROM game g
	JOIN player p ON g.batting_first = p.team_id
	JOIN deliveries d ON p.id = d.on_strike_batter_id
					  AND g.id = d.game_id
WHERE g.id = ?
GROUP BY p.id

would you be so kind as to point me in the right direction for extending your original sql (you will see some extensions that are working per player)

[Adamhumbug]

Answered my own question.

Apologies for my impatience.

SELECT p.first_name
	, p.last_name
	, p.id as playerid
	, sum(runs_scored) as runs
	, count(runs_scored) as balls
	, (Select count(runs_scored) from deliveries where runs_scored = 4 and deliveries.on_strike_batter_id = p.id) as fours
FROM game g
	JOIN player p ON g.batting_first = p.team_id
	JOIN deliveries d ON p.id = d.on_strike_batter_id
					  AND g.id = d.game_id
WHERE g.id = ?
GROUP BY p.id

 

[Barand]

1 minute ago, Adamhumbug said:

, (Select count(runs_scored) from deliveries where runs_scored = 4 and deliveries.on_strike_batter_id = p.id) as fours

Always avoid subqueries like that one - they really slow down the performance of the query.

Try

SELECT p.first_name
    , p.last_name
    , p.id as playerid
    , sum(runs_scored) as runs
    , count(runs_scored) as balls
    , sum(runs_scored = 4 ) as fours
    , sum(runs_scored = 6) as sixes
FROM game g
    JOIN player p ON g.batting_first = p.team_id
    JOIN deliveries d ON p.id = d.on_strike_batter_id
                      AND g.id = d.game_id
WHERE g.id = 51
GROUP BY p.id;

+------------+-----------+----------+------+-------+-------+-------+
| first_name | last_name | playerid | runs | balls | fours | sixes |
+------------+-----------+----------+------+-------+-------+-------+
| Brian      | Lara      |       20 |   58 |    19 |     7 |     2 |
| Freddie    | Flintoff  |       21 |   46 |    14 |     6 |     2 |
+------------+-----------+----------+------+-------+-------+-------+

 

[Adamhumbug]

2 minutes ago, Barand said:

Always avoid subqueries like that one - they really slow down the performance of the query.

Try

SELECT p.first_name
    , p.last_name
    , p.id as playerid
    , sum(runs_scored) as runs
    , count(runs_scored) as balls
    , sum(runs_scored = 4 ) as fours
    , sum(runs_scored = 6) as sixes
FROM game g
    JOIN player p ON g.batting_first = p.team_id
    JOIN deliveries d ON p.id = d.on_strike_batter_id
                      AND g.id = d.game_id
WHERE g.id = 51
GROUP BY p.id;

+------------+-----------+----------+------+-------+-------+-------+
| first_name | last_name | playerid | runs | balls | fours | sixes |
+------------+-----------+----------+------+-------+-------+-------+
| Brian      | Lara      |       20 |   58 |    19 |     7 |     2 |
| Freddie    | Flintoff  |       21 |   46 |    14 |     6 |     2 |
+------------+-----------+----------+------+-------+-------+-------+

 

Amazing - thanks.

 

I will try and avoid using them.