嵌套SQL语句与AVG()

我有三个表，我想从中提取数据。一个是类表，它存储所提供的类的列表，包括id、时间戳、培训师id、开始时间、上午/下午和上课天数。培训师id是一个外键，它将其绑定到一个培训师表，我在其中根据培训师的唯一id提取培训师名称。

这很好，但是我还需要显示每节课的平均出勤率。这涉及到一个具有类id外键的统计表，该表将类id与该会话的参与者数量一起放入一行中。我想返回每个类的每个课程的平均参与者数。

SELECT 
                        class_id AS id, 
                        class_name, 
                        class_trainerid,
                        class_starttime AS start, 
                        class_ampm AS ampm,
                        class_days AS days,
                        trainer_id AS trainid,
                        trainer_name,
                        stat_class AS sclass,
                        AVG(stat_students) as stat_students_avg
                    FROM 
                        $class_table
                        LEFT JOIN $trainer_table ON (class_trainerid = trainer_id)
                        LEFT JOIN stats ON (id = stat_students_avg)
                    GROUP BY
                        id

上面的代码可能表明我实际上并不知道如何做到这一点。我看过关于通过连接平均的帖子，或者在选择语句中使用选择语句，但我似乎无法将这些转换为我的问题。

编辑:下面是类表模式:

`class_id` tinyint(8) NOT NULL AUTO_INCREMENT,
`class_datereated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`class_name` varchar(256) NOT NULL,
`class_trainerid` tinyint(8) NOT NULL,
`class_starttime` time NOT NULL,
`class_ampm` text NOT NULL,
`class_days` text NOT NULL,
PRIMARY KEY (`class_id`)

下面是训练器模式

`trainer_id` tinyint(8) NOT NULL AUTO_INCREMENT,
'trainer_datecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`trainer_name` varchar(256) NOT NULL,
`trainer_password` varchar(25) NOT NULL,
`trainer_email` varchar(256) NOT NULL,
`trainer_active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`trainer_id`),
UNIQUE KEY `email` (`trainer_email`)

，下面是stats表模式:

`stat_id` int(8) NOT NULL AUTO_INCREMENT,
`stat_datecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_class` int(8) NOT NULL,
`stat_students` int(8) NOT NULL,
`stat_trainer` tinyint(8) NOT NULL,
`stat_class_date` date NOT NULL,
PRIMARY KEY (`stat_id`)

输出将是php while语句的一部分，结果如下:

echo "<table class='list'>";
        echo "<tr>";
        echo "<th>Class Name</th><th>Trainer</th><th>Ave</th><th>Edit</th><th>Delete</th>";
        echo "</tr>";
        while($row = mysql_fetch_assoc($class_result))
        {
            echo "<tr id='class_".$row["id"]."'>"; 
            echo "<td>".$row["class_name"]."</td>";
            echo "<td class='trainer-name'>".$row["trainer_name"]."</td>";
            echo "<td class='trainer-name'>".$row["stat_students_avg"]."</td>";
            echo "<td class='icon'><a href='javascript:void(0);' id='class_edit_".$row["id"]."'><span class='glyphicon glyphicon-edit'></span></a></td>";
            echo "<td class='icon'><a href='javascript:void(0);' id='class_delete_".$row["id"]."'><span class='glyphicon glyphicon-remove'></span></a></td>";
            echo "</tr>";
        }
        echo "</table>";