如何计算每所大学的学生人数

yftpprvb  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(321)

我有一个大学生的sql数据库,详细信息如下:

Table_name: register
    Column_names: position, tertinst

数据库中的数据如下所示:

Coach..........UCT
    Athlete........Tukkies
    Official.......University of JHB
    Athlete........Tukkies
    Athlete........Tshwane Tech
    Manager........UCT

我需要计算每所大学(tertinst)运动员(职位)的学生数量,输出必须如下:

UCT.....................735
   University of Jhb.......668
   Tukkies.................886

这是我的尝试:

$positionx = 'Athletes';

include_once 'core/includes/db_connect.php';

$sql = "SELECT tertinst, COUNT(position) FROM register WHERE position = '$positionx' GROUP BY tertinst ";

$result = $conn->query($sql);
while ($row = mysql_fetch_array($result)) {
    echo $row['COUNT(tertinst)'] . '......' . $row['COUNT(position)'];
}

$conn->close();

当代码被执行时,我没有得到任何结果,并且我已经搜索了几个小时不同的解决方案,但没有成功。

gstyhher

gstyhher1#

我犯了一些错误,并更正了sql计数中的语法以及echo。以下是解决我问题的方法:

<?php

include_once 'core/includes/db_connect.php';

$sql = "SELECT tertinst, COUNT(*) total FROM register WHERE position = 'Athletes' GROUP BY tertinst ";
$result = $conn->query($sql);

while ($row = $result->fetch_assoc()) {
    $tertinst = $row["tertinst"];

    echo $tertinst . '......' . $row['total'] . '<br>';
}
$conn->close();

相关问题