現在エンジニアを目指してPHPを学習中です。
SQLで集計関数の結果をJOINして取得する方法を学んだので、メモです。

前提

以下の2つのテーブル（生徒、テスト結果）があったとして、
各生徒の科目毎の平均点を算出した上で、生徒テーブルにJOINして取得したい
というのが今回やりたいことです。
ちなみにMySQLは5.7.34

↓生徒テーブル

↓テスト結果テーブル（）

↓取得したいデータ

先に結論：「JOIN hoge ON」のhogeに集計関数のSQLをはめる

色々調べた結果、今回は以下のSQLを流すことで希望のデータを取得できました。

SELECT * FROM 生徒 LEFT JOIN (SELECT 生徒id, AVG(英語), AVG(数学) FROM review GROUP BY 生徒id) AS avg ON 生徒.id = avg.生徒id

順を追って見ていきましょう

はじめに、集計結果を取得する関数を用意します。集計関数は以下が代表的なものらしい。
（記載方法がわかりやすくなるよう、SELECTもつけてます）

SELECT COUNT(x) /* xの件数を取得 */
SELECT AVG(x) /* xの平均を取得 */
SELECT SUM(x) /* xの合計を取得 */
SELECT MIN(x) /* xのなかで最小値を取得 */
SELECT MAX(x) /* xのなかで最大値を取得 */

今回であれば、テスト結果テーブルから生徒ごとに英語・数学の平均点を取得したいです。
以下のSQLで、平均点を取得できます。

SELECT 生徒id, AVG(英語), AVG(数学) FROM テスト結果 GROUP BY 生徒ID

今回は、生徒テーブルを基準に、平均点をJOINしたデータを出したいです。
なので以下。（WHERE句などついていても問題ありません）

SELECT * FROM 生徒　LEFT JOIN /**/ ON 生徒.id = テスト結果.生徒id

②のSQLにおける/**/の部分に①を結合して完成です。
なお①の関数にはASで別名をつけます。今回はavgとしました。
ONの部分も若干書き換わっているので注意です。

SELECT * FROM 生徒 LEFT JOIN (SELECT 生徒id, AVG(英語), AVG(数学) FROM review GROUP BY 生徒id) AS avg ON 生徒.id = avg.生徒id

今回の考え方ですが、
①の集計関数で新たなテーブルを作成し、③で生徒テーブルと新テーブル（avgと命名）を結合した
と考えるとわかりやすいかなと思います。
↓avg テーブル

そのため、①のSQLでは後々の結合に必要な生徒idも取得しておかないと、③でエラーになります。
↓以下はエラー

SELECT AVG(英語), AVG(数学) FROM テスト結果 GROUP BY 生徒ID

今回は以上です。SQL深めたい。。

※内容や考え方に間違い、補足等ありましたらぜひ教えていただけますと幸いです！