Fitur GMAIL untuk Tempat pesan file dokumen yang sudah terkirim

Pada kesempatan ini kita akan membahas mengenai cara untuk menghitung total dan subtotal pada MySQL,  setidaknya ada tiga cara yang dapat kita lakukan, yaitu:

1. Membuat script seperti PHP untuk melakukan penjumlahan sehingga menghasilkan total atau subtotal
2. Menggunakan ROLLUP pada query MySQL
3. Melakukan penggabungan query

Catatan: Artikel ini cukup panjang, jika ingin langsung ke topik yang diinginkan, silakan klik salah satu link diatas.

Artikel lain untuk menjumlah kolom MySQL: Menghitung Field/Kolom Pada Tabel MySQL Dengan Kondisi Tertentu Menggunakan COUNT IF

Sebelum lebih lanjut membahas ketiga cara diatas, sobat bisa langsung praktek dengan mengunduh file sql yang digunakan dalam artikel ini. file ini akan membuat tabel sales yang berisi sekitar 400 data penjualan dengan layout seperti pada tabel berikut:

+--------------+-----------+------------+---------+---------+---------+---------+--------------+
| id_transaksi | id_produk | tgl_byr    | thn_byr | jml_byr | jns_byr | id_bank | id_pelanggan |
+--------------+-----------+------------+---------+---------+---------+---------+--------------+
|            1 |       100 | 2016-09-20 |    2016 |  265000 |       1 |      16 |            1 |
|            2 |       100 | 2016-10-11 |    2016 |  270000 |       1 |      24 |            2 |
|            3 |       101 | 2016-08-17 |    2016 |  250000 |       1 |      22 |            2 |
|            9 |       101 | 2016-08-24 |    2016 |  380000 |       1 |      16 |            2 |
|           11 |       101 | 2016-05-10 |    2016 |  250000 |       1 |       1 |            1 |
+--------------+-----------+------------+---------+---------+---------+---------+--------------+

Download File SQLDownload ALL Files

I. Menghitung Total dan Subtotal Pada MySQL dengan PHP

Mungkin cara inilah yang sering kita gunakan, karena praktis dan mudah untuk di praktekkan, cara tersebut kurang lebih seperti ini:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

output yang dihasilkan:

ID PELANGGANID PRODUKTOTAL110028.885.000110138.225.000210025.855.000210141.295.000TOTAL134.260.000

walaupun mudah dilakukan, namun terdapat beberapa kelemahan, yaitu:

• Ketika hasil query digunakan pada bahasa pemrograman lain, kita harus menulis ulang kode nya (porting),
• Pada situasi tertentu misal ketika harus membuat sub total, maka kode yang kita tulis semakin rumit tergantung kompleksitas layout yang ingin kita buat. misal kita ingin mendapatkan output berikut:

TAHUNID PELANGGANID PRODUKTOTAL2015110013.660.0002015110119.885.000SUB TOTAL33.545.0002015210015.145.0002015210119.595.000SUB TOTAL34.740.000SUB TOTAL 201568.285.0002016110015.225.0002016110118.340.000SUB TOTAL33.565.0002016210010.710.0002016210121.700.000SUB TOTAL32.410.000SUB TOTAL 201665.975.000GRAND TOTAL134.260.000

Maka script PHP yang telah kita buat tapi perlu kita ubah menjadi:

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

Perhatikan baris ke 7, pada baris tersebut kita simpan semua hasil query ke dalam variabel

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

Jika row yang dihasilkan sedikit, penyimpanan ke dalam variabel 

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

Mempertimbangkan berbagai kekurangan diatas, saya pribadi berpendapat sebaiknya cara ini digunakan sebagai alternatif terakhir  ketika memang tidak ada cara lain yang bisa digunakan, misal pada pivot table yang kompleks yang melibatkan banyak fungsi agregasi.

II. Menggunakan WITH ROLLUP Pada Query MySQL

Pada MySQL, klausa 

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

Kelebihan klausa ini adalah simpel dan dapat menghasilkan total dan subtotal hanya dalam satu query, sehingga membuat pekerjaan kita jauh lebih mudah dan sederhana.

Misal, untuk menyelesaikan pembuatan total dan subtotal sebagaimana contoh sebelumnya, kita hanya perlu menambahkan

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

itu saja cukup? ya itu saja, simpel kan? ….. mari kita lanjutkan, ketika kita jalankan query tersebut (menggunakan HEIDI SQL), hasil yang kita peroleh adalah:

jika kita perhatikan, MySQL melakukan tiga fungsi penjumlahan berdasarkan kolom yang ada di

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

1. SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   7 MySQL menambahkan baris baru untuk menjumlahkan kolom

   SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   8 per

   SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   9 dengan memberikan nilai NULL pada kolom 

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   0 dan

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   1

2. SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   9 dan 

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   3 MySQL menambahkan baris baru untuk menjumlahkan kolom

   SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   8 per (

   SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   9 dan

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   6 dengan memberikan nilai NULL pada kolom 

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   1

3. SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   9,

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   0 dan

   +---------+--------------+-----------+-----------+
   | thn_byr | id_pelanggan | id_produk | jml_byr   |
   +---------+--------------+-----------+-----------+
   | 2015    | 1            | 100       |  13660000 |
   | 2015    | 1            | 101       |  19885000 |
   | 2015    | 1            | SUB TOTAL |  33545000 |
   | 2015    | 2            | 100       |  15145000 |
   | 2015    | 2            | 101       |  19595000 |
   | 2015    | 2            | SUB TOTAL |  34740000 |
   | 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
   | 2016    | 1            | 100       |  15225000 |
   | 2016    | 1            | 101       |  18340000 |
   | 2016    | 1            | SUB TOTAL |  33565000 |
   | 2016    | 2            | 100       |  10710000 |
   | 2016    | 2            | 101       |  21700000 |
   | 2016    | 2            | SUB TOTAL |  32410000 |
   | 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
   | TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
   +---------+--------------+-----------+-----------+

   0 MySQL menambahkan baris baru untuk menjumlahkan kolom

   SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   8 untuk per (

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   1,

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   0 dan

   +---------+--------------+-----------+-----------+
   | thn_byr | id_pelanggan | id_produk | jml_byr   |
   +---------+--------------+-----------+-----------+
   | 2015    | 1            | 100       |  13660000 |
   | 2015    | 1            | 101       |  19885000 |
   | 2015    | 1            | SUB TOTAL |  33545000 |
   | 2015    | 2            | 100       |  15145000 |
   | 2015    | 2            | 101       |  19595000 |
   | 2015    | 2            | SUB TOTAL |  34740000 |
   | 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
   | 2016    | 1            | 100       |  15225000 |
   | 2016    | 1            | 101       |  18340000 |
   | 2016    | 1            | SUB TOTAL |  33565000 |
   | 2016    | 2            | 100       |  10710000 |
   | 2016    | 2            | 101       |  21700000 |
   | 2016    | 2            | SUB TOTAL |  32410000 |
   | 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
   | TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
   +---------+--------------+-----------+-----------+

   4 dengan memberikan nilai NULL pada kolom

   SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   9,

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   0 dan

   SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
   		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
   		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
   		SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   1.

Perhatikan kolom yang diberikan tanda kotak merah, kenapa nilainya seperti itu? Nilai pada kolom tersebut, dan kolom sejenis yang dihasilkan oleh klausa GROUP BY dipilih secara bebas oleh MySQL, namun biasanya mengikuti nilai pada baris sebelumnya.

1 Mengganti Nilai Null

Jika kita perhatikan, nilai NULL kurang bermakna, sehingga kita perlu untuk mengubahnya.

Kita dapat mengubah NULL dengan nilai lain menggunakan fungsi

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

Catatan: di MSSQL kita harus mengganti

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

<?php
echo '<table class="grey">
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID FRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
while ($row = $stmt->fetch())
{
	$class = '';
	
	// jika ada total atau sub total
	if ($row['thn_byr'] == 'TOTAL') {
		$class = ' class="total"';
	} else if ($row['id_pelanggan'] == 'SUB TOTAL' || $row['id_produk'] == 'SUB TOTAL') {
		$class = ' class="subtotal"';
	}
	
	echo 
	'<tr'.$class.'>
		<td>' . $row['thn_byr'] . '</td>
		<td>' . $row['id_pelanggan'] . '</td>
		<td>' . $row['id_produk'] . '</td>
		<td class="right">' . number_format($row['jml_byr'], '0', ',', '.') . '</td>
	</tr>';
}
echo '
	</tbody>
</table>
</body>
</html>';

Dengan menggunakan 

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
		SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

output yang dihasilkan:

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

Jika ingin menampilkan tabel diatas pada browser, script PHP yang kita perlukan:

<?php
echo '<table class="grey">
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID FRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
while ($row = $stmt->fetch())
{
	$class = '';
	
	// jika ada total atau sub total
	if ($row['thn_byr'] == 'TOTAL') {
		$class = ' class="total"';
	} else if ($row['id_pelanggan'] == 'SUB TOTAL' || $row['id_produk'] == 'SUB TOTAL') {
		$class = ' class="subtotal"';
	}
	
	echo 
	'<tr'.$class.'>
		<td>' . $row['thn_byr'] . '</td>
		<td>' . $row['id_pelanggan'] . '</td>
		<td>' . $row['id_produk'] . '</td>
		<td class="right">' . number_format($row['jml_byr'], '0', ',', '.') . '</td>
	</tr>';
}
echo '
	</tbody>
</table>
</body>
</html>';

2 Menggunakan ORDER BY Pada WITH ROLLUP Pada MySQL

Untuk mengurutkan hasil query yang mengandung klausa 

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

<?php
echo '<table class="grey">
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID FRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
while ($row = $stmt->fetch())
{
	$class = '';
	
	// jika ada total atau sub total
	if ($row['thn_byr'] == 'TOTAL') {
		$class = ' class="total"';
	} else if ($row['id_pelanggan'] == 'SUB TOTAL' || $row['id_produk'] == 'SUB TOTAL') {
		$class = ' class="subtotal"';
	}
	
	echo 
	'<tr'.$class.'>
		<td>' . $row['thn_byr'] . '</td>
		<td>' . $row['id_pelanggan'] . '</td>
		<td>' . $row['id_produk'] . '</td>
		<td class="right">' . number_format($row['jml_byr'], '0', ',', '.') . '</td>
	</tr>';
}
echo '
	</tbody>
</table>
</body>
</html>';

Dengan Implisit Order

Ketika kita menjalankan klausa GROUP BY, sebenarnya, disaat yang sama, MySQL juga melakukan pengurutan data,  namun karena tidak dinyatakan dengan jelas, maka sering disebut implisit order.

Pada contoh sebelumnya terlihat bahwa kolom

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
		SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
		SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

Pada implisit order, kita dapat mengubah cara pengurutannya dengan menambahkan

<?php
echo '<table class="grey">
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID FRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
while ($row = $stmt->fetch())
{
	$class = '';
	
	// jika ada total atau sub total
	if ($row['thn_byr'] == 'TOTAL') {
		$class = ' class="total"';
	} else if ($row['id_pelanggan'] == 'SUB TOTAL' || $row['id_produk'] == 'SUB TOTAL') {
		$class = ' class="subtotal"';
	}
	
	echo 
	'<tr'.$class.'>
		<td>' . $row['thn_byr'] . '</td>
		<td>' . $row['id_pelanggan'] . '</td>
		<td>' . $row['id_produk'] . '</td>
		<td class="right">' . number_format($row['jml_byr'], '0', ',', '.') . '</td>
	</tr>';
}
echo '
	</tbody>
</table>
</body>
</html>';

SELECT	COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
	COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
	COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
	SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr DESC, id_pelanggan DESC, id_produk DESC
WITH ROLLUP

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
		SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
		SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT	COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
	COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
	COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
	SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr DESC, id_pelanggan DESC, id_produk DESC
WITH ROLLUP

Hasil yang kita peroleh:

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

Pada tabel diatas terlihat bahwa kolom tahun kolom,

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
		SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
		SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

Namun demikian, pada MySQL versi 5.7, fitur ini sudah deprecated artinya sudah tidak disarankan lagi untuk digunakan karena pada versi berikutnya fitur ini akan dihilangkan. Untuk itu, disarankan untuk menggunakan eksplisit order dengan menggunakan klausa

<?php
echo '<table class="grey">
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID FRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
while ($row = $stmt->fetch())
{
	$class = '';
	
	// jika ada total atau sub total
	if ($row['thn_byr'] == 'TOTAL') {
		$class = ' class="total"';
	} else if ($row['id_pelanggan'] == 'SUB TOTAL' || $row['id_produk'] == 'SUB TOTAL') {
		$class = ' class="subtotal"';
	}
	
	echo 
	'<tr'.$class.'>
		<td>' . $row['thn_byr'] . '</td>
		<td>' . $row['id_pelanggan'] . '</td>
		<td>' . $row['id_produk'] . '</td>
		<td class="right">' . number_format($row['jml_byr'], '0', ',', '.') . '</td>
	</tr>';
}
echo '
	</tbody>
</table>
</body>
</html>';

Dengan Eksplisit Order

Untuk dapat mengaplikasikan eksplisit order, terlebih dahulu kita buat temporary table (sub query) yang mengandung klausa 

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

<?php
echo '<table class="grey">
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID FRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
while ($row = $stmt->fetch())
{
	$class = '';
	
	// jika ada total atau sub total
	if ($row['thn_byr'] == 'TOTAL') {
		$class = ' class="total"';
	} else if ($row['id_pelanggan'] == 'SUB TOTAL' || $row['id_produk'] == 'SUB TOTAL') {
		$class = ' class="subtotal"';
	}
	
	echo 
	'<tr'.$class.'>
		<td>' . $row['thn_byr'] . '</td>
		<td>' . $row['id_pelanggan'] . '</td>
		<td>' . $row['id_produk'] . '</td>
		<td class="right">' . number_format($row['jml_byr'], '0', ',', '.') . '</td>
	</tr>';
}
echo '
	</tbody>
</table>
</body>
</html>';

Meneruskan contoh sebelumnya, kali ini kita urutkan kolom

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT thn_byr, id_pelanggan, id_produk, jml_byr
FROM
(
	SELECT COALESCE(thn_byr, 0) as thn_byr,
			COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
			COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
			SUM(jml_byr) AS jml_byr
    FROM sales
    GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP
) as sales
ORDER BY thn_byr DESC, id_pelanggan, jml_byr

Output yang dihasilkan adalah:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

Perhatikan bahwa pada baris ke 4, kita menggunakan perintah

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

Kita sengaja gunakan angka

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

3 Menggunakan LIMIT Pada WITH ROLLUP

Penggunaan klausa 

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2016    | 2            | 101       |  21700000 |
| 2016    | 2            | 100       |  10710000 |
| 2016    | 2            | SUB TOTAL |  32410000 |
| 2016    | 1            | 101       |  18340000 |
| 2016    | 1            | 100       |  15225000 |
| 2016    | 1            | SUB TOTAL |  33565000 |
| 2016    | SUB TOTAL    | SUB TOTAL |  65975000 |
| 2015    | 2            | 101       |  19595000 |
| 2015    | 2            | 100       |  15145000 |
| 2015    | 2            | SUB TOTAL |  34740000 |
| 2015    | 1            | 101       |  19885000 |
| 2015    | 1            | 100       |  13660000 |
| 2015    | 1            | SUB TOTAL |  33545000 |
| 2015    | SUB TOTAL    | SUB TOTAL |  68285000 |
| TOTAL   | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

Output yang dihasilkan adalah:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

Pada contoh diatas, terlihat bahwa MySQL mengambil 5 baris pertama hasil query termasuk baris yang dihasilkan oleh klausa WITH ROLLUP

III. Menggunakan Query Tersendiri Untuk Menghitung Total Pada MySQL

Menggunakan

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

Dengan cara ini kita menambahkan baris baru secara manual untuk menghitung total dan subtotal. Penambahan tersebut bisa langsung disertakan pada query utama atau pada sub query, tergantung kondisi yang ada.

Untuk menghitung total saja, kita cukup menambahkan query di bawah query utama, contoh:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

Hasil yang kita peroleh:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

Perhatikan bahwa dengan cara ketiga ini, kita dapat lebih mudah melakukan pengolahan data.

Pada contoh diatas, kita dapat mengurutkan data pembayaran mulai dari yang terbesar hingga terkecil dan sekaligus mengurutkan tahun dari yang terbesar hingga terkecil dengan hasil yang rapi, hal tersebut cukup sulit dilakukan jika menggunakan WITH ROLLUP.

1 Menggunakan Berbagai Fungsi Agregasi

Penggunaan query secara manual juga memungkinkan kita untuk melakukan berbagai fungsi agregasi.

Contoh berikut ini sedikit memodifikasi contoh sebelumya, kali ini kita akan:

• Mengurutkan kolom

  SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
  FROM `sales` 
  GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

  9 menurun dari yang paling besar ke yang paling kecil,

  SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
  		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
  		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
  		SUM(jml_byr) AS jml_byr 
  FROM `sales` 
  GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

  0 dari yang paling kecil ke yang paling besar, dan

  SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
  FROM `sales` 
  GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

  8 dari yang paling besar ke yang paling kecil. Semuanya  dikelompolkkan per

  SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
  		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
  		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
  		SUM(jml_byr) AS jml_byr 
  FROM `sales` 
  GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

  3
• Disamping itu, kita juga tambahkan kolom rasio yang berisi persentase nilai pembayaran dibanding jumlah seluruh pembayaran

Kode SQLnya adalah:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

output yang dihasilkan adalah:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

Perhatikan bahwa kita menyimpan nilai grand total (nilai yang kita gunakan untuk menghitung rasio) ke dalam variabel

SELECT thn_byr, id_pelanggan, id_produk, jml_byr
FROM
(
	SELECT COALESCE(thn_byr, 0) as thn_byr,
			COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
			COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
			SUM(jml_byr) AS jml_byr
    FROM sales
    GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP
) as sales
ORDER BY thn_byr DESC, id_pelanggan, jml_byr

Pada contoh diatas, kita kumpulkan kata-kata 1-SUB TOTAL, 2-SUB TOTAL, TOTAL 2016, TOTAL 2015 dan GRAND TOTAL pada kolom

SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
		SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

Bentuk kata-kata tersebut diatur sedemikian rupa sehingga ketika diurutkan, output yang dihasilkan sesuai dengan yang kita inginkan, contoh untuk tahun 2016, 1-SUB TOTAL lebih besar dari 1, sehingga di tempatkan di bawah 1, demikian juga 2 dan TOTAL 2016.

2 Penambahan Kolom Baru

Untuk menghasilkan layout seperti diatas memang benar-benar tergantung pada pemilihan kata yang akan diurutkan, jika pemilihan katanya tidak tepat bisa jadi menghasilkan urutan yang berbeda.

Misal pada perintah sql sebelumnya, baris ke 8 kita ganti perintahnya dari

SELECT thn_byr, id_pelanggan, id_produk, jml_byr
FROM
(
	SELECT COALESCE(thn_byr, 0) as thn_byr,
			COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
			COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
			SUM(jml_byr) AS jml_byr
    FROM sales
    GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP
) as sales
ORDER BY thn_byr DESC, id_pelanggan, jml_byr

SELECT thn_byr, id_pelanggan, id_produk, jml_byr
FROM
(
	SELECT COALESCE(thn_byr, 0) as thn_byr,
			COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
			COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
			SUM(jml_byr) AS jml_byr
    FROM sales
    GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP
) as sales
ORDER BY thn_byr DESC, id_pelanggan, jml_byr

thn_byrid_pelangganid_produkjml_byrrasio201611011834000013.66201611001522500011.34201621012170000016.1620162100107100007.982016SUB TOTAL-11003356500025.002016SUB TOTAL-21003241000024.142016TOTAL 20161006597500049.14201511011988500014.81201511001366000010.17201521011959500014.59201521001514500011.282015SUB TOTAL-11003354500025.882015SUB TOTAL-21003474000024.992015TOTAL 20151006828500050.86NULLGRAND TOTALNULL134260000100.00

dari tabel diatas terlihat bahwa urutannya berubah, tidak sesuai yang diharapkan. Bagaimana cara mengatasinya?

Ada cara lain yaitu menambahkan kolom baru untuk

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

• kita tidak melakukan group (

  SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
  FROM `sales` 
  GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

  6) dan
• tersedia urutan yang jelas antar row, misal ada kolom tanggal atau id yang berurutan.

3 Membuat Temporary Index

Kondisi menjadi sulit jika tidak ada urutan yang jelas antar row, query yang dilakukan membutuhkan logika yang cukup kompleks.

Untuk mengatasi ini, kita perlu menciptakan temporary field berisi indeks (urutan) dari baris, melanjutkan contoh sebelumnya:

1. Pertama kita akan buat temporary field bernama

   <?php
   $sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
   		FROM `sales` 
   		GROUP BY id_pelanggan, id_produk';
   $stmt = $pdo->prepare($sql);
   $stmt->execute();
   
   echo '<table>
   		<thead>
   			<tr>
   				<td>ID PELANGGAN</td>
   				<td>ID PRODUK</td>
   				<td>TOTAL</td>
   			</tr>
   		</thead>
   		<tbody>';
   $total = 0;
   while ($row = $stmt->fetch())
   {
   	echo '<tr>
   			<td>' . $row['id_pelanggan'] . '</td>
   			<td>' . $row['id_produk'] . '</td>
   			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
   		</tr>';
   	$total += $row['total'];
   }
   echo '<tr>
   		<td colspan="2">TOTAL</td>
   		<td>' . number_format($total, 0, ',', '.') . '</td>
   	 </tr>
   	</tbody>
   </table>';

   03 yang berisi urutan baris.
2. Selanjutnya nilai

   <?php
   $sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
   		FROM `sales` 
   		GROUP BY id_pelanggan, id_produk';
   $stmt = $pdo->prepare($sql);
   $stmt->execute();
   
   echo '<table>
   		<thead>
   			<tr>
   				<td>ID PELANGGAN</td>
   				<td>ID PRODUK</td>
   				<td>TOTAL</td>
   			</tr>
   		</thead>
   		<tbody>';
   $total = 0;
   while ($row = $stmt->fetch())
   {
   	echo '<tr>
   			<td>' . $row['id_pelanggan'] . '</td>
   			<td>' . $row['id_produk'] . '</td>
   			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
   		</tr>';
   	$total += $row['total'];
   }
   echo '<tr>
   		<td colspan="2">TOTAL</td>
   		<td>' . number_format($total, 0, ',', '.') . '</td>
   	 </tr>
   	</tbody>
   </table>';

   03 tersebut kita gunakan untuk membuat query lagi sehingga kita dapatkan nilai tahun pada row berikutnya yang pada contoh kali ini kita simpan pada kolom

   <?php
   $sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
   		FROM `sales` 
   		GROUP BY id_pelanggan, id_produk';
   $stmt = $pdo->prepare($sql);
   $stmt->execute();
   
   echo '<table>
   		<thead>
   			<tr>
   				<td>ID PELANGGAN</td>
   				<td>ID PRODUK</td>
   				<td>TOTAL</td>
   			</tr>
   		</thead>
   		<tbody>';
   $total = 0;
   while ($row = $stmt->fetch())
   {
   	echo '<tr>
   			<td>' . $row['id_pelanggan'] . '</td>
   			<td>' . $row['id_produk'] . '</td>
   			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
   		</tr>';
   	$total += $row['total'];
   }
   echo '<tr>
   		<td colspan="2">TOTAL</td>
   		<td>' . number_format($total, 0, ',', '.') . '</td>
   	 </tr>
   	</tbody>
   </table>';

   05

perintah sql nya adalah:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

hasil yang kita dapatkan:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

Perhatikan bahwa kita menggunakan variabel @idx untuk menyimpan urutan baris utama dan @idx2 untuk menyimpan urutan baris berikutnya (nilai @idx2 adalah @idx + 1).

Query Selanjutnya…

Setelah kita ketahui nilai

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

Jika nilainya tidak sama (seperti pada baris yang di bold), maka kita tahu bahwa baris tersebut adalah baris terakhir dari tahun berjalan, sehingga kita lakukan penjumlahan untuk mendapatkan subtotal tahun tersebut.

Kita ubah querynya menjadi:

<?php
$sql = 'SELECT id_pelanggan, id_produk, SUM(jml_byr) AS total 
		FROM `sales` 
		GROUP BY id_pelanggan, id_produk';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>ID PELANGGAN</td>
				<td>ID PRODUK</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['id_pelanggan'] . '</td>
			<td>' . $row['id_produk'] . '</td>
			<td>' . number_format($row['total'], 0, ',', '.') . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total, 0, ',', '.') . '</td>
	 </tr>
	</tbody>
</table>';

hasil yang kita peroleh:

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

perhatikan perintah SQL pada baris ke 20, kita hitung subtotal untuk tahun yang sama ketika nilai pada variabel @idx1 dan @idx2 berbeda. Cara diatas dapat menginspirasi kita untuk membuat subtotal per 

SELECT COALESCE(thn_byr, 'TOTAL') AS thn_byr, 
		COALESCE(id_pelanggan, 'SUB TOTAL') AS id_pelanggan,
		COALESCE(id_produk, 'SUB TOTAL') AS id_produk,
		SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

Cara terakhir ini lebih aman, setidaknya ketika kita ubah urutan tahunnya, nilai pada kolom sub_total akan mengikutinya.

Coba pada perintah sql terakhir, kita ubah semua kata DESC menjadi ASC, maka data akan diurutkan berdasarkan nilai

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
FROM `sales` 
GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

Namun demikian, terdapat beberapa kekurangan pada cara ini, diantaranya memerlukan query yang lebih kompleks, sehingga semakin besar jumlah data yang diolah, maka semakin lama waktu eksekusinya.

VI. Kesimpulan

Dari pembahasan diatas dapat disimpulkan bahwa untuk menghitung total dan subtotal pada mysql, setidaknya ada tiga cara yang dapat digunakan, yaitu:

1. Penghitungan dilakukan di level program aplikasi, seperti PHP, cara ini cenderung lebih mudah digunakan karena logika yang didukung pada bahasa level aplikasi lebih banyak daripada bahasa SQL.

   Namun demikian, sebaiknya cara ini digunakan jika query tersebut tidak akan di jalankan pada aplikasi dengan bahasa pemrograman lain, karena kita harus menulis ulang (porting) script, menyesuaikan dengan aplikasi yang baru.

2. Menggunakan 

   SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   1. Penggunaan

   SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   1 lebih simpel dan mudah, namun memiliki beberapa keterbatasan diantaranya hanya terbatas untuk fungsi penjumlahan.
3. Menggunakan penggabungan query. Cara ini lebih fleksibel dibanding menggunakan

   SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) AS jml_byr 
   FROM `sales` 
   GROUP BY thn_byr, id_pelanggan, id_produk WITH ROLLUP

   1 karena kita dapat melakukan berbagai fungsi agregasi. Penggunaan cara ini akan membuat perintah sql menjadi lebih kompleks sehingga terdapat potensi menurunkan performa database.

dari ketiga cara diatas, mana yang sebaiknya kita gunakan? semua kembali kepada kondisi yang ada, saya pribadi penganut prinsip “sekali jadi”, dimana pengolahan data sebisa mungkin selesai diperintah SQL.

Demikian pembahasan mengenai menghitung total dan subtotal pada MySQL, semoga bermanfaat.

Subscibe Now

Suka dengan artikel di Jagowebdev.com? jangan sampai ketinggalan, segera join ke milis kami untuk update informasi terbaru dari Jagowebdev.com

Komitmen Kami: Kami senantiasa menghargai privasi Anda dan tidak akan membagikan identitas Anda ke pihak manapun.

Wandia says:

12 January 2017 at 16:21

Halo mas, kalau boleh minta tutorialnya untuk penerapan mysql pada script php untuk menghitung total dari beberapa item. Sejauh ini saya baru mempelajari php untuk menghitung total item yang dipilih yaitu dengan menggunakan input checkbox.

Nah lalu bagaimana cara menghitung total apabila item yang ingin dihitung didapatkan dari tabel mysql. Misal saya punya tabel seperti ini:

|——- Item ——- | —- Berat per Pack —- |
| Gula | 500 |
| Kopi | 250 |
| Telur | 1000 |
|——————– | ———————— |

Dari tabel diatas dijadikan dalam bentuk mysql dan untuk form php nya kira-kira seperti ini:

[ ] Gula
[ ] Kopi
[ ] Telur

Misalkan berat per pack dalam satuan gram dan untuk harga per gram anggaplah sama untuk semua item yaitu Rp.500

Jadi dari tabel mysql dan form php tersebut nanti bisa dimunculkan total harganya dari item yang di ceklis.

Terima kasih sebelumnya.

Reply

Wandia says:

20 January 2017 at 22:49

Halo mas berkaitan dengan yang pernah saya tanyakan di #comment-1330 sebelumnya. Saya sudah bisa menerapkan dari contoh script yang mas berikan tersebut.

Tujuan saya adalah ingin membuat semacam form yang isinya adalah:
1. Beberapa item yang bisa dipilih dengan checkbox. Nama item (kopi, gula, tepung, dll) dan beratnya (dalam gram) diambil dari database mysql seperti yg sebelumnya dicontohkan.
2. Text input seperti nama, alamat, email, dll
3. Tampilan summary/hasil dari pengisian form tersebut. Yang ditampilkan adalah semua item yang sudah dipilih melalui checkbox, total penjumlahan berat dari semua item yang dipilih, dan semua text input yang sudah diisikan.

Saya sudah berhasil menerapkannya. Terdapat 2 halaman yang saya buat, yang pertama adalah form.html dan yang kedua adalah summary.php yang isinya adalah tampilan hasil output form tadi.

Yang saya tanyakan adalah bagaimana supaya tampilan dari summary.php tersebut selanjutnya juga bisa dikirimkan ke email, tapi dengan bentuk teks yang tersusun rapi per baris, bukan langsung sederet tulisan.

Terima kasih.

Reply

Harjono says:

16 April 2018 at 02:12

malam mas, saya ada beberapa pertanyaan.. kiranya sudi membantu.
saya punya database seperti dibawah ini:

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

kemudian untuk di tampilan webnya saya ingin ada 1 combo box dan 2 text box,
1. combobox asal (dengan opsi Kota A dan Kota B yang didapat dari query database kolom Asal)
2. textbox tujuan dengan autocomplete (yang didapat dari query database kolom Tujuan)
3. Textbox Harga yang menampilkan hasil tarif perhitungan otomatis dari asal & tujuan

mohon bantuannya untuk script PHP yang simple untuk model diatas.
sebelumnya saya ucapkan terimakasih.

Reply

Ahmad says:

21 May 2018 at 09:54

pak saya dibantu scriptnya php mysql dong…. saya bingung menampilkan table spd bulan pertama, spd bulan kedua, dan spd bulan ketiga,,

ya script phpnya seperti dibawah ini pak…

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

Reply

Haris86 says:

17 October 2018 at 19:04

mohon pencerahan mas
Saya mau buat query dari 4 tabel dibawah

Struktur Tabel:

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

Trus tabel data yg diinput kayak gini:

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

Pertanyaannya gini mas., saya mau gabungin data itu buat tampilin trus saya mau hitung nilainya untuk total sama mau di kalikan dengan data dari T.score sehinggal dapat nilai Score lalu dihitung lagi buat dapat sub total dari score., jadi nanti hasilnya kayak dibawah ini mas

T.Hasil

<?php
function format_ribuan ($nilai){
	return number_format ($nilai, 0, ',', '.');
}

// Ubah hasil query menjadi associative array dan simpan kedalam variabel result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>TAHUN</th>
				<th>ID PELANGGAN</th>
				<th>ID PRODUK</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_plg = $subtotal_thn = $total = 0;
foreach ($result as $key => $row)
{
	$subtotal_plg += $row['jml_byr'];
	$subtotal_thn += $row['jml_byr'];
	echo '<tr>
			<td>'.$row['thn_byr'].'</td>
			<td>'.$row['id_pelanggan'].'</td>
			<td>'.$row['id_produk'].'</td>
			<td class="right">'.format_ribuan($row['jml_byr']).'</td>
		</tr>';
	
	// SUB TOTAL per id_pelanggan
	if (@$result[$key+1]['id_pelanggan'] != $row['id_pelanggan']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_plg).'</td>
		</tr>';
		$subtotal_plg = 0;
	}
	
	// SUB TOTAL per thn_byr
	if (@$result[$key+1]['thn_byr'] != $row['thn_byr']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['thn_byr'] . '</td>
			<td></td>
			<td class="right">'.format_ribuan($subtotal_thn).'</td>
		</tr>';
		$subtotal_thn = 0;
	} 
	$total += $row['jml_byr'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . format_ribuan($total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

mohon pencerahan mas

Reply

Edwin says:

23 November 2018 at 09:27

jml penerimaan tgl
51 PUSKESMAS 2018-01-02
15 DOKTER PRAKTEK 2018-01-02
78 PUSKESMAS 2018-01-03
20 DOKTER PRAKTEK 2018-01-03

Met pagi pak agus, saya mau menanyakan bagaiaman caranya memisah hasil jumlah dan penerimaanx tersendiri, sehingga hasilx jml1,jml2, penerimaan1,penerimaan2,tgl ??