Jump to content

For each in mysql query


Adamhumbug
Go to solution Solved by Barand,

Recommended Posts

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 by Adamhumbug
Link to comment
Share on other sites

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 by Adamhumbug
Link to comment
Share on other sites

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 by Adamhumbug
Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 |
+------------+-----------+----------+------+-------+-------+-------+

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.