Bagaimana cara menggunakan GROUP BY pada while (perulangan) PHP?

Saya memiliki database dengan nama table adalah 'siswa' yang memiliki kolom seperti berikut

image.png

dan table keadaan_siswa yang memiliki kolom berikut

image.png

Lalu saya ingin mencari seperti table berikut,

image.png

terlihat pada foto ke-3 berhasil menampilkan data yang sesuai diinginkan. Tetapi saat saya menambah data siswa dengan kecamatan yang berbeda maka akan muncul seperti gambar berikut,

image.png

terlihat pada lingkaran biru tidak muncul nama kecamatan siswa yang baru saja di tambahkan. Sehingga tabel hancur

Saya mempunyai sebanyak 3 macam perulangan didalam 1 table,

dan setiap perulangannya sudah berhasil menampilkan data yang diingkan (berhasil),

yang jadi pertanyaan adalah bagaimana caranya agar bisa GROUP BY id_kecamatan?

berikut adalah codingan saya

<table id="example" class="stripe row-border order-column display table table-striped table-bordered" cellspacing="0" width="100%"><!--table 2-->
<thead>
<tr>
<th rowspan="3" colspan=""><center>KECAMATAN</center></th>
<th rowspan="2" colspan="2"><center>KELAS ROMBEL</center></th>
<th colspan="3"><center>BULAN LALU</center></th>
<th colspan="2"><center>KELUAR</center></th>
<th colspan="2"><center>MASUK</center></th>
<th colspan="3"><center>BULAN INI</center></th>
<th colspan="7"><center>SISWA MENURUT AGAMA</center></th>
<th colspan="8"><center>SISWA MENURUT UMUR (TAHUN)</center></th>
</tr>

<tr>
<th><center>L</center></th>
<th><center>P</center></th>
<th><center>JLH</center></th>

<th><center>L</center></th>
<th><center>P</center></th>

<th><center>L</center></th>
<th><center>P</center></th>

<th><center>L</center></th>
<th><center>P</center></th>
<th><center>JLH</center></th>

<th><center>ISLAM</center></th>
<th><center>KRISTEN PROTESTAN</center></th>
<th><center>KRISTEN KATOLIK</center></th>
<th><center>HINDU</center></th>
<th><center>BUDHA</center></th>
<th><center>KONGHUCU</center></th>
<th><center>JLH</center></th>
<th><center>=/<11</center></th>
<th><center>12</center></th>
<th><center>13</center></th>
<th><center>14</center></th>
<th><center>15</center></th>
<th><center>16</center></th>
<th><center>=/>17</center></th>
<th><center>JLH</center></th>
</tr>

<tr>
<th><center><i>1</i></center></th>
<th><center><i>2</i></center></th>
<th><center><i>3</i></center></th>
<th><center><i>4</i></center></th>
<th><center><i>5</i></center></th>

<th><center><i>6</i></center></th>
<th><center><i>7</i></center></th>
<th><center><i>8</i></center></th>
<th><center><i>9</i></center></th>
<th><center><i>10</i></center></th>

<th><center><i>11</i></center></th>
<th><center><i>12</i></center></th>
<th><center><i>13</i></center></th>
<th><center><i>14</i></center></th>
<th><center><i>15</i></center></th>

<th><center><i>16</i></center></th>
<th><center><i>17</i></center></th>
<th><center><i>18</i></center></th>
<th><center><i>19</i></center></th>
<th><center><i>20</i></center></th>

<th><center><i>21</i></center></th>
<th><center><i>22</i></center></th>
<th><center><i>23</i></center></th>
<th><center><i>24</i></center></th>
<th><center><i>25</i></center></th>

<th><center><i>26</i></center></th>
<th><center><i>27</i></center></th>
</tr>
</thead>

<tbody>
<?php
$sql = mysql_query("SELECT a.*, b.nm_kecamatan AS nm_kecamatan, c.nm_sekolah AS nm_sekolah,
sum(a.bulan_lalu_laki_vii) AS bulan_lalu_laki_vii, sum(a.bulan_lalu_perempuan_vii) AS bulan_lalu_perempuan_vii, sum(a.keluar_laki_vii) AS keluar_laki_vii, sum(a.keluar_perempuan_vii) AS keluar_perempuan_vii, sum(a.masuk_laki_vii) AS masuk_laki_vii, sum(a.masuk_perempuan_vii) AS masuk_perempuan_vii, sum(a.bulan_ini_laki_vii) AS bulan_ini_laki_vii, sum(a.bulan_ini_perempuan_vii) AS bulan_ini_perempuan_vii,
sum(a.bulan_lalu_laki_viii) AS bulan_lalu_laki_viii, sum(a.bulan_lalu_perempuan_viii) AS bulan_lalu_perempuan_viii, sum(a.keluar_laki_viii) AS keluar_laki_viii, sum(a.keluar_perempuan_viii) AS keluar_perempuan_viii, sum(a.masuk_laki_viii) AS masuk_laki_viii, sum(a.masuk_perempuan_viii) AS masuk_perempuan_viii, sum(a.bulan_ini_laki_viii) AS bulan_ini_laki_viii, sum(a.bulan_ini_perempuan_viii) AS bulan_ini_perempuan_viii,
sum(a.bulan_lalu_laki_ix) AS bulan_lalu_laki_ix, sum(a.bulan_lalu_perempuan_ix) AS bulan_lalu_perempuan_ix, sum(a.keluar_laki_ix) AS keluar_laki_ix, sum(a.keluar_perempuan_ix) AS keluar_perempuan_ix, sum(a.masuk_laki_ix) AS masuk_laki_ix, sum(a.masuk_perempuan_ix) AS masuk_perempuan_ix, sum(a.bulan_ini_laki_ix) AS bulan_ini_laki_ix, sum(a.bulan_ini_perempuan_ix) AS bulan_ini_perempuan_ix
FROM keadaan_siswa a
JOIN kecamatan b ON a.id_kecamatan = b.id_kecamatan
JOIN sekolah c ON a.id_sekolah = c.id_sekolah
GROUP BY id_kecamatan
ORDER BY id_kecamatan ASC");

while ($data = mysql_fetch_assoc($sql))
{
?>
<tr>
<td rowspan="3"><?php echo $data['nm_kecamatan'];?></td>

<td><center>VII</center></td>
<td><center>0</center></td>
<td><center><?php echo $data['bulan_lalu_laki_vii'];?></center></td>
<td><center><?php echo $data['bulan_lalu_perempuan_vii'];?></center></td>
<th><center><?php echo $data['bulan_lalu_laki_vii']+$data['bulan_lalu_perempuan_vii'];?></center></th>

<td><center><?php echo $data['keluar_laki_vii'];?></center></td>
<td><center><?php echo $data['keluar_perempuan_vii'];?></center></td>
<td><center><?php echo $data['masuk_laki_vii'];?></center></td>
<td><center><?php echo $data['masuk_perempuan_vii'];?></center></td>

<td><center><?php echo $data['bulan_ini_laki_vii'];?></center></td>
<td><center><?php echo $data['bulan_ini_perempuan_vii'];?></center></td>
<th><center><?php echo $data['bulan_ini_laki_vii']+$data['bulan_ini_perempuan_vii'];?></center></th>

<?php
$sql1 = mysql_query("SELECT a.*, b.nm_kecamatan AS nm_kecamatan, c.nm_sekolah AS nm_sekolah,
sum(a.agama_siswa='Islam' AND rombel_siswa='VII') AS agama_siswa_islam_vii, sum(a.agama_siswa='Protestan' AND rombel_siswa='VII') AS agama_siswa_protestan_vii, sum(a.agama_siswa='Katolik' AND rombel_siswa='VII') AS agama_siswa_katolik_vii, sum(a.agama_siswa='Buddha' AND rombel_siswa='VII') AS agama_siswa_buddha_vii, sum(a.agama_siswa='Hindu'AND rombel_siswa='VII') AS agama_siswa_hindu_vii, sum(a.agama_siswa='Konghucu' AND rombel_siswa='VII') AS agama_siswa_konghucu_vii,
sum(a.agama_siswa='Islam' AND rombel_siswa='VIII') AS agama_siswa_islam_viii, sum(a.agama_siswa='Protestan' AND rombel_siswa='VIII') AS agama_siswa_protestan_viii, sum(a.agama_siswa='Katolik' AND rombel_siswa='VIII') AS agama_siswa_katolik_viii, sum(a.agama_siswa='Buddha' AND rombel_siswa='VIII') AS agama_siswa_buddha_viii, sum(a.agama_siswa='Hindu'AND rombel_siswa='VIII') AS agama_siswa_hindu_viii, sum(a.agama_siswa='Konghucu' AND rombel_siswa='VIII') AS agama_siswa_konghucu_viii,
sum(a.agama_siswa='Islam' AND rombel_siswa='IX') AS agama_siswa_islam_ix, sum(a.agama_siswa='Protestan' AND rombel_siswa='IX') AS agama_siswa_protestan_ix, sum(a.agama_siswa='Katolik' AND rombel_siswa='IX') AS agama_siswa_katolik_ix, sum(a.agama_siswa='Buddha' AND rombel_siswa='IX') AS agama_siswa_buddha_ix, sum(a.agama_siswa='Hindu'AND rombel_siswa='IX') AS agama_siswa_hindu_ix, sum(a.agama_siswa='Konghucu' AND rombel_siswa='IX') AS agama_siswa_konghucu_ix
FROM siswa a
JOIN kecamatan b ON a.id_kecamatan = b.id_kecamatan
JOIN sekolah c ON a.id_sekolah = c.id_sekolah
GROUP BY id_kecamatan
ORDER BY id_kecamatan ASC");
while ($data1 = mysql_fetch_assoc($sql1))
{

?>

<td><center><?php echo $data1['agama_siswa_islam_vii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_protestan_vii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_katolik_vii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_buddha_vii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_hindu_vii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_konghucu_vii'];?></center></td>
<th><center><?php echo $data1['agama_siswa_islam_vii']+$data1['agama_siswa_protestan_vii']+$data1['agama_siswa_katolik_vii']+$data1['agama_siswa_buddha_vii']+$data1['agama_siswa_hindu_vii']+$data1['agama_siswa_konghucu_vii'];?></center></th>

<?php
$sql_umur = mysql_query("SELECT
COUNT(IF(a.umur <=11,1,NULL)) AS umur11,
COUNT(IF(a.umur=12,1,NULL)) AS umur12,
COUNT(IF(a.umur=13,1,NULL)) AS umur13,
COUNT(IF(a.umur=14,1,NULL)) AS umur14,
COUNT(IF(a.umur=15,1,NULL)) AS umur15,
COUNT(IF(a.umur=16,1,NULL)) AS umur16,
COUNT(IF(a.umur>=17,1,NULL)) AS umur17
FROM (SELECT TIMESTAMPDIFF(YEAR, tanggal_lahir, CURDATE())
AS umur
FROM siswa
WHERE rombel_siswa = 'VII' AND id_kecamatan = '$data1[id_kecamatan]' ) a
");
while ($data_umur = mysql_fetch_assoc($sql_umur))
{
?>

<td><center><?php echo $data_umur['umur11'];?></center></td>
<td><center><?php echo $data_umur['umur12'];?></center></td>
<td><center><?php echo $data_umur['umur13'];?></center></td>

<td><center><?php echo $data_umur['umur14'];?></center></td>
<td><center><?php echo $data_umur['umur15'];?></center></td>
<td><center><?php echo $data_umur['umur16'];?></center></td>
<td><center><?php echo $data_umur['umur17'];?></center></td>
<td><center>x</center></td>
<?php
}
?>
</tr>

<tr>
<td><center>VIII</center></td>
<td><center>0</center></td>
<td><center><?php echo $data['bulan_lalu_laki_viii'];?></center></td>
<td><center><?php echo $data['bulan_lalu_perempuan_viii'];?></center></td>
<th><center><?php echo $data['bulan_lalu_laki_viii']+$data['bulan_lalu_perempuan_viii'];?></center></th>

<td><center><?php echo $data['keluar_laki_viii'];?></center></td>
<td><center><?php echo $data['keluar_perempuan_viii'];?></center></td>
<td><center><?php echo $data['masuk_laki_viii'];?></center></td>
<td><center><?php echo $data['masuk_perempuan_viii'];?></center></td>

<td><center><?php echo $data['bulan_ini_laki_viii'];?></center></td>
<td><center><?php echo $data['bulan_ini_perempuan_viii'];?></center></td>
<th><center><?php echo $data['bulan_ini_laki_viii']+$data['bulan_ini_perempuan_viii'];?></center></th>

<td><center><?php echo $data1['agama_siswa_islam_viii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_protestan_viii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_katolik_viii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_buddha_viii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_hindu_viii'];?></center></td>
<td><center><?php echo $data1['agama_siswa_konghucu_viii'];?></center></td>
<th><center><?php echo $data1['agama_siswa_islam_viii']+$data1['agama_siswa_protestan_viii']+$data1['agama_siswa_katolik_viii']+$data1['agama_siswa_buddha_viii']+$data1['agama_siswa_hindu_viii']+$data1['agama_siswa_konghucu_viii'];?></center></th>

<?php
$sql_umur = mysql_query("SELECT
COUNT(IF(a.umur <=11,1,NULL)) AS umur11,
COUNT(IF(a.umur=12,1,NULL)) AS umur12,
COUNT(IF(a.umur=13,1,NULL)) AS umur13,
COUNT(IF(a.umur=14,1,NULL)) AS umur14,
COUNT(IF(a.umur=15,1,NULL)) AS umur15,
COUNT(IF(a.umur=16,1,NULL)) AS umur16,
COUNT(IF(a.umur>=17,1,NULL)) AS umur17
FROM (SELECT TIMESTAMPDIFF(YEAR, tanggal_lahir, CURDATE())
AS umur FROM siswa
WHERE rombel_siswa = 'VIII'
AND id_kecamatan = '$data1[id_kecamatan]' ) a
");
while ($data_umur = mysql_fetch_assoc($sql_umur))
{
?>

<td><?php echo $data_umur['umur11'];?></td>
<td><?php echo $data_umur['umur12'];?></td>
<td><?php echo $data_umur['umur13'];?></td>

<td><?php echo $data_umur['umur14'];?></td>
<td><?php echo $data_umur['umur15'];?></td>
<td><?php echo $data_umur['umur16'];?></td>
<td><?php echo $data_umur['umur17'];?></td>
<td><?php echo $data_umur['umur17'];?></td>
<?php
}
?>
</tr>

<tr>
<td><center>IX</center></td>
<td><center>0</center></td>
<td><center><?php echo $data['bulan_lalu_laki_ix'];?></center></td>
<td><center><?php echo $data['bulan_lalu_perempuan_ix'];?></center></td>
<th><center><?php echo $data['bulan_lalu_laki_ix']+$data['bulan_lalu_perempuan_ix'];?></center></th>

<td><center><?php echo $data['keluar_laki_ix'];?></center></td>
<td><center><?php echo $data['keluar_perempuan_ix'];?></center></td>
<td><center><?php echo $data['masuk_laki_ix'];?></center></td>
<td><center><?php echo $data['masuk_perempuan_ix'];?></center></td>

<td><center><?php echo $data['bulan_ini_laki_ix'];?></center></td>
<td><center><?php echo $data['bulan_ini_perempuan_ix'];?></center></td>
<th><center><?php echo $data['bulan_ini_laki_ix']+$data['bulan_ini_perempuan_ix'];?></center></th>

<td><center><?php echo $data1['agama_siswa_islam_ix'];?></center></td>
<td><center><?php echo $data1['agama_siswa_protestan_ix'];?></center></td>
<td><center><?php echo $data1['agama_siswa_katolik_ix'];?></center></td>
<td><center><?php echo $data1['agama_siswa_buddha_ix'];?></center></td>
<td><center><?php echo $data1['agama_siswa_hindu_ix'];?></center></td>
<td><center><?php echo $data1['agama_siswa_konghucu_ix'];?></center></td>
<th><center><?php echo $data1['agama_siswa_islam_ix']+$data1['agama_siswa_protestan_ix']+$data1['agama_siswa_katolik_ix']+$data1['agama_siswa_buddha_ix']+$data1['agama_siswa_hindu_ix']+$data1['agama_siswa_konghucu_ix'];?></center></th>

<?php
$sql_umur = mysql_query("SELECT
COUNT(IF(a.umur <=11,1,NULL)) AS umur11,
COUNT(IF(a.umur=12,1,NULL)) AS umur12,
COUNT(IF(a.umur=13,1,NULL)) AS umur13,
COUNT(IF(a.umur=14,1,NULL)) AS umur14,
COUNT(IF(a.umur=15,1,NULL)) AS umur15,
COUNT(IF(a.umur=16,1,NULL)) AS umur16,
COUNT(IF(a.umur>=17,1,NULL)) AS umur17
FROM (SELECT TIMESTAMPDIFF(YEAR, tanggal_lahir, CURDATE())
AS umur
FROM siswa
WHERE rombel_siswa = 'IX'
AND id_kecamatan = '$data1[id_kecamatan]' ) a
");
while ($data_umur = mysql_fetch_assoc($sql_umur))
{
?>

<td><?php echo $data_umur['umur11'];?></td>
<td><?php echo $data_umur['umur12'];?></td>
<td><?php echo $data_umur['umur13'];?></td>

<td><?php echo $data_umur['umur14'];?></td>
<td><?php echo $data_umur['umur15'];?></td>
<td><?php echo $data_umur['umur16'];?></td>
<td><?php echo $data_umur['umur17'];?></td>
<td>x</td>
<?php
}
?>
</tr>
<?php

}
}
?>
</tbody>
</table>

saya sudah mencoba untuk menambahkan GROUP BY id_kecamatan ke semua query tetapi masalah yang muncul

image.png

mohon bantuannya gan?