Menghitung Total dan Subtotal Pada MySQL

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 |
+--------------+-----------+------------+---------+---------+---------+---------+--------------+

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:

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:

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>';

Download File PHP

Perhatikan baris ke 7, pada baris tersebut kita simpan semua hasil query ke dalam variabel $result hal ini harus dilakukan karena untuk membuat subtotal kita harus mengetahui nilai baris berikutnya (baris 33 dan 44),

Jika row yang dihasilkan sedikit, penyimpanan ke dalam variabel $result tidak akan membebani memori komputer, namun sebaliknya jika jumlah rownya banyak, kemungkinan dapat membebani memori karena data yang disimpan ke dalam variabel $result semakin besar, yang akibatnya aplikasi berjalan lambat.

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 WITH ROLLUP digunakan bersamaan dengan klausa GROUP BY,  WITH ROLLUP dapat digunakan baik untuk menghitung total maupun sub total sesuai dengan kolom yang kita gunakan dalam klausa GROUP BY.

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 WITH ROLLUP pada akhir query, sehingga querynya menjadi seperti ini:

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 group by

1. thn_byr. MySQL menambahkan baris baru untuk menjumlahkan kolom jml_byr per thn_byr dengan memberikan nilai NULL pada kolom id_pelanggan dan id_produk
2. thn_byr dan id_pelanggan. MySQL menambahkan baris baru untuk menjumlahkan kolom jml_byr per (thn_byr dan id_pelanggan) dengan memberikan nilai NULL pada kolom id_produk
3. thn_byr, id_pelanggan dan id_produk. MySQL menambahkan baris baru untuk menjumlahkan kolom jml_byr untuk per (id_produk, id_pelanggan dan thn_byr) dengan memberikan nilai NULL pada kolom thn_byr, id_pelanggan dan id_produk.

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 COALESCE, beberapa ada yang menggunakan fungsi IFNULL, saya sendiri prefer menggunakan  COALESCE karena termasuk dalam ANSI SQL standar (92) sehingga kompatibel dengan bahasa SQL pada database lain, seperti  MSSQL.

Catatan: di MSSQL kita harus mengganti IFNULL dengan ISNULL.

Dengan menggunakan COALESCE, perintah SQL diatas kita ubah menjadi:

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>';

Download File PHP Script Diatas

2 Menggunakan ORDER BY Pada WITH ROLLUP Pada MySQL

Untuk mengurutkan hasil query yang mengandung klausa WITH ROLLUP, kita tidak bisa langsung menggunakan klausa ORDER BY, karena sifat keduanya independen (berdiri sendiri), untuk itu, terdapat dua cara yang dapat kita lakukan yaitu dengan implisit order dan eksplisit order:

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 thn_byr diurutkan secara ascending (urut dari nilai terkecil ke nilai terbesar), demikian juga pada kolom id_pelanggan dan id_produk

Pada implisit order, kita dapat mengubah cara pengurutannya dengan menambahkan ASC atau DESC pada klausa GROUP BY, misal, pada contoh sebelumnya kita balik urutan data pada kolom, thn_byr, id_pelanggan, dan id_produk

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, thn_byr, id_pelanggan, dan id_produk telah diurutkan secara descending.

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 ORDER BY

Dengan Eksplisit Order

Untuk dapat mengaplikasikan eksplisit order, terlebih dahulu kita buat temporary table (sub query) yang mengandung klausa WITH ROLLUP, selanjutnya urutkan data tersebut menggunakan klausa ORDER BY yang ada pada query utama.

Meneruskan contoh sebelumnya, kali ini kita urutkan kolom thn_byr dari nilai terbesar ke terkecil:

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:

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
|    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 |
|    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 |
|       0 | SUB TOTAL    | SUB TOTAL | 134260000 |
+---------+--------------+-----------+-----------+

Perhatikan bahwa pada baris ke 4, kita menggunakan perintah COALESCE(thn_byr, 0) as thn_byr bukan COALESCE(thn_byr, 'TOTAL') as thn_byr.

Kita sengaja gunakan angka 0 untuk mencerminkan total karena thn_byr akan diurutkan mengecil sehingga angka 0 akan berada di pisisi paling bawah, jika kita gunakan COALESCE(thn_byr, 'TOTAL'), maka baris tersebut akan berada di posisi paling atas.

3 Menggunakan LIMIT Pada WITH ROLLUP

Penggunaan klausa LIMIT pada WITH ROLLUP terkadang menghasilkan output yang tidak sesuai harapan, karena tambahan baris baru yang dihasilkan oleh kalusa WITH ROLLUP akan dimasukkan dalam penghitungan baris, contoh:

SELECT thn_byr, 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
LIMIT 5

Output yang dihasilkan adalah:

+---------+--------------+-----------+----------+
| 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 |
+---------+--------------+-----------+----------+

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 WITH ROLLUP cukup memadahi untuk menghitung total dan subtotal, namun memiliki keterbatasan yaitu hanya dapat melakukan operasi penjumlahan, permasalahan tersebut dapat diatasi dengan cara ketiga ini.

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:

SELECT thn_byr, id_pelanggan, id_produk, jml_byr
FROM
(
	SELECT thn_byr, 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
	ORDER BY thn_byr DESC, jml_byr DESC
) as sales
UNION ALL
SELECT 'TOTAL', null, null, SUM(jml_byr) AS jml_byr 
FROM sales

Hasil yang kita peroleh:

+---------+--------------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr   |
+---------+--------------+-----------+-----------+
| 2016    |            2 | 101       |  21700000 |
| 2016    |            1 | 101       |  18340000 |
| 2016    |            1 | 100       |  15225000 |
| 2016    |            2 | 100       |  10710000 |
| 2015    |            1 | 101       |  19885000 |
| 2015    |            2 | 101       |  19595000 |
| 2015    |            2 | 100       |  15145000 |
| 2015    |            1 | 100       |  13660000 |
| TOTAL   |         NULL | NULL      | 134260000 |
+---------+--------------+-----------+-----------+

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 thn_byr menurun dari yang paling besar ke yang paling kecil, id_pelanggan dari yang paling kecil ke yang paling besar, dan jml_byr dari yang paling besar ke yang paling kecil. Semuanya  dikelompolkkan per id_pelanggan.
• Disamping itu, kita juga tambahkan kolom rasio yang berisi persentase nilai pembayaran dibanding jumlah seluruh pembayaran

Kode SQLnya adalah:

SELECT SUM(jml_byr) FROM sales INTO @total;

SELECT thn_byr, id_pelanggan, id_produk, SUM(jml_byr) as jml_byr, ROUND(SUM(jml_byr)/@total*100, 2) as rasio
FROM sales
GROUP BY thn_byr, id_pelanggan, id_produk
	UNION
SELECT thn_byr, CONCAT(id_pelanggan, '-SUB TOTAL'), id_produk, SUM(jml_byr) as jml_byr, ROUND(SUM(jml_byr)/@total*100, 2) as rasio
FROM sales
GROUP BY thn_byr, id_pelanggan
	UNION
SELECT thn_byr, CONCAT('TOTAL ', thn_byr), id_produk, SUM(jml_byr) as jml_byr, ROUND(SUM(jml_byr)/@total*100, 2) as rasio
FROM sales
GROUP BY thn_byr
	UNION
SELECT null, 'GRAND TOTAL', null, ROUND(@total), '100.00'
ORDER BY thn_byr DESC, id_pelanggan, jml_byr DESC

output yang dihasilkan adalah:

+---------+--------------+-----------+-----------+--------+
| thn_byr | id_pelanggan | id_produk | jml_byr   | rasio  |
+---------+--------------+-----------+-----------+--------+
|    2016 | 1            |       101 |  18340000 | 13.66  |
|    2016 | 1            |       100 |  15225000 | 11.34  |
|    2016 | 1-SUB TOTAL  |       100 |  33565000 | 25.00  |
|    2016 | 2            |       101 |  21700000 | 16.16  |
|    2016 | 2            |       100 |  10710000 | 7.98   |
|    2016 | 2-SUB TOTAL  |       100 |  32410000 | 24.14  |
|    2016 | TOTAL 2016   |       100 |  65975000 | 49.14  |
|    2015 | 1            |       101 |  19885000 | 14.81  |
|    2015 | 1            |       100 |  13660000 | 10.17  |
|    2015 | 1-SUB TOTAL  |       100 |  33545000 | 24.99  |
|    2015 | 2            |       101 |  19595000 | 14.59  |
|    2015 | 2            |       100 |  15145000 | 11.28  |
|    2015 | 2-SUB TOTAL  |       100 |  34740000 | 25.88  |
|    2015 | TOTAL 2015   |       100 |  68285000 | 50.86  |
|    NULL | GRAND TOTAL  |      NULL | 134260000 | 100.00 |
+---------+--------------+-----------+-----------+--------+

Perhatikan bahwa kita menyimpan nilai grand total (nilai yang kita gunakan untuk menghitung rasio) ke dalam variabel @total. Hal ini kita lakukan karena nilai tersebut digunakan lebih dari satu kali dan tidak efisien jika dilakukan query berulang kali.

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

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 CONCAT(id_pelanggan, '-SUB TOTAL') menjadi CONCAT(‘SUB TOTAL-', id_pelanggan), maka hasil yang diperoleh:

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

Ada cara lain yaitu menambahkan kolom baru untuk sub_total dan total, cara ini tidak sulit dilakukan jika:

• kita tidak melakukan group (group by) 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 idx yang berisi urutan baris.
2. Selanjutnya nilai idx tersebut kita gunakan untuk membuat query lagi sehingga kita dapatkan nilai tahun pada row berikutnya yang pada contoh kali ini kita simpan pada kolom idx_next_year

perintah sql nya adalah:

SET @idx=0, @idx2=0;

SELECT SUM(jml_byr) FROM sales INTO @total;
SELECT thn_byr, id_pelanggan, id_produk, jml_byr, rasio
		,@idx:=@idx+1 as idx
		,
				(      
					SELECT CONCAT(rnum, '-', thn_byr)
					FROM
					(
						SELECT thn_byr, @idx2:=@idx2+1 as rnum
						 FROM 
						 (
							 SELECT  thn_byr FROM sales GROUP BY thn_byr DESC, id_pelanggan, id_produk
						 ) as tmp
					) as tmp
					WHERE rnum > @idx AND rnum < @idx+2   
				)
			as idx_next_year
FROM (
    SELECT thn_byr, id_pelanggan, id_produk
			,SUM(jml_byr) as jml_byr
			,ROUND(SUM(jml_byr)/@total*100, 2) as rasio
    FROM sales
    GROUP BY thn_byr, id_pelanggan, id_produk
    ORDER BY thn_byr DESC, id_pelanggan
) new_sales

hasil yang kita dapatkan:

+---------+--------------+-----------+----------+-------+------+---------------+
| thn_byr | id_pelanggan | id_produk | jml_byr  | rasio | idx  | idx_next_year |
+---------+--------------+-----------+----------+-------+------+---------------+
|    2016 |            1 |       100 | 15225000 | 11.34 |    1 | 2-2016        |
|    2016 |            1 |       101 | 18340000 | 13.66 |    2 | 3-2016        |
|    2016 |            2 |       100 | 10710000 |  7.98 |    3 | 4-2016        |
|    2016 |            2 |       101 | 21700000 | 16.16 |    4 | 5-2015        |
|    2015 |            1 |       100 | 13660000 | 10.17 |    5 | 6-2015        |
|    2015 |            1 |       101 | 19885000 | 14.81 |    6 | 7-2015        |
|    2015 |            2 |       100 | 15145000 | 11.28 |    7 | 8-2015        |
|    2015 |            2 |       101 | 19595000 | 14.59 |    8 | NULL          |
+---------+--------------+-----------+----------+-------+------+---------------+

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 thn_byr row berikutnya (kolom idx_next_year), kita tinggal menguji nilai thn_byr tersebut.

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:

SET @idx=0, @idx2=0;

SELECT SUM(jml_byr) FROM sales INTO @total;
SELECT thn_byr, id_pelanggan, id_produk, jml_byr, rasio
		,@idx:=@idx+1 as idx
		,CASE WHEN thn_byr =
				(      
					SELECT thn_byr
					FROM
					(
						SELECT thn_byr, @idx2:=@idx2+1 as rnum
						 FROM 
						 (
							 SELECT  thn_byr FROM sales GROUP BY thn_byr DESC, id_pelanggan, id_produk
						 ) as tmp
					) as tmp
					WHERE rnum > @idx AND rnum < @idx+2   
				)
			THEN ''
			ELSE (SELECT SUM(jml_byr) FROM sales WHERE thn_byr = new_sales.thn_byr)
			END as sub_total
		,IF (@idx = (SELECT COUNT(thn_byr) 
					FROM (SELECT thn_byr FROM sales GROUP BY thn_byr, id_pelanggan, id_produk) as tmp)
			, (SELECT SUM(jml_byr) FROM sales)
			, '') as total
FROM (
    SELECT thn_byr, id_pelanggan, id_produk
			,SUM(jml_byr) as jml_byr
			,ROUND(SUM(jml_byr)/@total*100, 2) as rasio
    FROM sales
    GROUP BY thn_byr, id_pelanggan, id_produk
    ORDER BY thn_byr DESC, id_pelanggan
) new_sales

hasil yang kita peroleh:

+---------+--------------+-----------+----------+-------+------+-----------+-----------+
| thn_byr | id_pelanggan | id_produk | jml_byr  | rasio | idx  | sub_total | total     |
+---------+--------------+-----------+----------+-------+------+-----------+-----------+
|    2016 |            1 |       100 | 15225000 | 11.34 |    1 |           |           |
|    2016 |            1 |       101 | 18340000 | 13.66 |    2 |           |           |
|    2016 |            2 |       100 | 10710000 |  7.98 |    3 |           |           |
|    2016 |            2 |       101 | 21700000 | 16.16 |    4 | 65975000  |           |
|    2015 |            1 |       100 | 13660000 | 10.17 |    5 |           |           |
|    2015 |            1 |       101 | 19885000 | 14.81 |    6 |           |           |
|    2015 |            2 |       100 | 15145000 | 11.28 |    7 |           |           |
|    2015 |            2 |       101 | 19595000 | 14.59 |    8 | 68285000  | 134260000 |
+---------+--------------+-----------+----------+-------+------+-----------+-----------+

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 id_pelanggan, yang saya yakin sobat semua lebih mahir melakukannya.

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 thn_byr mulai dari yang terkecil hingga paling besar dan nilai pada kolom sub_total akan mengikutinya.

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 WITH ROLLUP. Penggunaan WITH ROLLUP lebih simpel dan mudah, namun memiliki beberapa keterbatasan diantaranya hanya terbatas untuk fungsi penjumlahan.
3. Menggunakan penggabungan query. Cara ini lebih fleksibel dibanding menggunakan WITH ROLLUP 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.