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:
- Membuat script seperti PHP untuk melakukan penjumlahan sehingga menghasilkan total atau subtotal
- Menggunakan ROLLUP pada query MySQL
- 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:
ID PELANGGAN | ID PRODUK | TOTAL |
---|---|---|
1 | 100 | 28.885.000 |
1 | 101 | 38.225.000 |
2 | 100 | 25.855.000 |
2 | 101 | 41.295.000 |
TOTAL | 134.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:
TAHUN | ID PELANGGAN | ID PRODUK | TOTAL |
---|---|---|---|
2015 | 1 | 100 | 13.660.000 |
2015 | 1 | 101 | 19.885.000 |
SUB TOTAL | 33.545.000 | ||
2015 | 2 | 100 | 15.145.000 |
2015 | 2 | 101 | 19.595.000 |
SUB TOTAL | 34.740.000 | ||
SUB TOTAL 2015 | 68.285.000 | ||
2016 | 1 | 100 | 15.225.000 |
2016 | 1 | 101 | 18.340.000 |
SUB TOTAL | 33.565.000 | ||
2016 | 2 | 100 | 10.710.000 |
2016 | 2 | 101 | 21.700.000 |
SUB TOTAL | 32.410.000 | ||
SUB TOTAL 2016 | 65.975.000 | ||
GRAND TOTAL | 134.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>';
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
thn_byr.
MySQL menambahkan baris baru untuk menjumlahkan kolomjml_byr
perthn_byr
dengan memberikan nilai NULL pada kolomid_pelanggan
danid_produk
thn_byr
danid_pelanggan.
MySQL menambahkan baris baru untuk menjumlahkan kolomjml_byr
per (thn_byr
danid_pelanggan)
dengan memberikan nilai NULL pada kolomid_produk
thn_byr
,id_pelanggan
danid_produk.
MySQL menambahkan baris baru untuk menjumlahkan kolomjml_byr
untuk per (id_produk
,id_pelanggan
danthn_byr)
dengan memberikan nilai NULL pada kolomthn_byr
,id_pelanggan
danid_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, danjml_byr
dari yang paling besar ke yang paling kecil. Semuanya dikelompolkkan perid_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:
thn_byr | id_pelanggan | id_produk | jml_byr | rasio |
---|---|---|---|---|
2016 | 1 | 101 | 18340000 | 13.66 |
2016 | 1 | 100 | 15225000 | 11.34 |
2016 | 2 | 101 | 21700000 | 16.16 |
2016 | 2 | 100 | 10710000 | 7.98 |
2016 | SUB TOTAL-1 | 100 | 33565000 | 25.00 |
2016 | SUB TOTAL-2 | 100 | 32410000 | 24.14 |
2016 | TOTAL 2016 | 100 | 65975000 | 49.14 |
2015 | 1 | 101 | 19885000 | 14.81 |
2015 | 1 | 100 | 13660000 | 10.17 |
2015 | 2 | 101 | 19595000 | 14.59 |
2015 | 2 | 100 | 15145000 | 11.28 |
2015 | SUB TOTAL-1 | 100 | 33545000 | 25.88 |
2015 | SUB TOTAL-2 | 100 | 34740000 | 24.99 |
2015 | TOTAL 2015 | 100 | 68285000 | 50.86 |
NULL | GRAND TOTAL | NULL | 134260000 | 100.00 |
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:
- Pertama kita akan buat temporary field bernama
idx
yang berisi urutan baris. - 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 kolomidx_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:
- 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.
- Menggunakan
WITH ROLLUP
. PenggunaanWITH ROLLUP
lebih simpel dan mudah, namun memiliki beberapa keterbatasan diantaranya hanya terbatas untuk fungsi penjumlahan. - 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.
Subscibe Now
Suka dengan artikel di Jagowebdev.com? jangan sampai ketinggalan, segera join ke milis kami untuk update informasi terbaru dari Jagowebdev.com
111 Feedback dari pembaca
mas link nya sudah tidak bisa untuk download lagi harap di cek ulang
Terima kasih mas infonya, sudah saya perbaiki
mas kalo membatasi total sks gimana ya…. minsalnya krs yang di ambil hanya bisa berjumlah 10 sks…… itu gimana ya mas…. kalo menjumlahin seluruhnya si bisa…. tapi untuk membatasi total sksnya belum bisa… mohon pencerahan nya ya mas 😀
Untuk membatasi data, gunakan klausa LIMIT mas, tapi bisa jadi penggunaannya tidak simpel, tergantung struktur datanya. Mungkin bisa dicontohkan datanya seperti apa mas?
serius saya sangat bingung dengan keadaan penjumlahan total ini..
tolong bantu saya donk..
berikan scriptya yang lengkap..
terima kasih
Coba bisa dicontohkan datanya seperti apa mas? sama hasil yang diinginkan seperti apa?
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.
Coba seperti ini mas:
Tabel:
Ketika dicentang kopi sama telur, outputnya:
ITEM: kopi BERAT: 250
ITEM: telur BERAT: 1000
Terima kasih atas bantuannya, jadi saya bisa mempelajari dari contoh script yang diberikan.
Untuk perhitungan total datanya saya mempelajari dari contoh script lain yang saya temukan sebagai bahan referensi untuk belajar.
saya menambahkan beberapa baris dari contoh script diatas.
Sebelum foreach
$berat = 0;
dan kemudian
$berat+= $ukuran;
setelah baris ini
$berat = key_exists($item_post, $item_db) ? $item_db[$item_post] : ”;
dan selanjutnya saya bisa mengkalkulasikan nilai harga dari item yang dipilih melalui checkbox.
Sekali lagi terima kasih atas bantuannya. Jangan bosan-bosan ya mas kalau mungkin nanti saya ada pertanyaan lagi hehe..
Contoh script PHP yang sudah dibuat seperti apa ya mas?
Sama contoh hasil yang diinginkan seperti apa mas?
Kalo penjumlahan yg di bab pertama.. bukannya g perlu disum mas… cukup $total +=$row[jml_bayar];
Saya coba hasilnya sama mas,.
Apakah ada pengaruhnya?
Sum yang diquery untuk menghasilkan kolom total mas, sedangkan di script php nya menghasilkan row total yang paling bawah
Tutorialnya bagus sekali mas, apa bisa saya bertanya
Jika saya punya kolom yang menyebutkan 5 digit angka dan ingin memecah perdigitnya, pakai script seperti apa?, misalkan
Kolom 1 isinya : 12345
Maka otomatis 5 kolom selanjutnya berisi perdigit tersebut, jadinya:
Kolom 2 : 1
Kolom 3 : 2
Kolom 4 : 3
Dst…
Terima kasih sebelumnya.
Coba menggunakan SUBSTRING mas untuk memotong teks, cuman MySQL tidak bisa benar benar dinamis artinya jika panjang teks tidak sama maka tidak bisa dihandle oleh MySQL
Mas klo mau menjumlahkan
Misal
Harga 50000
Jumlah beli 5
Biaya kirim 20000
Itu script nya gmna ya
Msh pemula mas hehe
Trimaksh sblmnya
Itu sumbernya dari apa ya mas? kalau hanya variabel biasa tinggal di jumlahkan mas, misal:
$total = ($harga * $jumlah_beli) + $biaya_kirim
mas kalau menghitung total jumlah jam gimana ya?
Ada contoh data dan contoh hasil yang diinginkan mas?
mau tanya mas…
sy punya dua tabel, table pertama deskripsi, table kedua yg mau di jumlahan…
kasus..
jika tabel satu kolom 2 = “Sum”
maka tabel dua kolom 1, 2 dan tiga di jumlakan dan kolom 4 dari table 2 adalah hasil sum
jika tabel satu kolom 2 = “Average”
maka tabel dua 1,2 dan tiga itu dibagi dan kolom 4 tabel dua itu hasil pembagian
jika tabel satu kolom 2 = “Last Value”
maka tabel dua kolom 4 hanya mengambil nilai dari kolom 3 pada tabel dua
mohon dibantu ya mas.. thanks before
Agar persepsi kita sama, ada contoh tabel data dan contoh hasil tabel yang diinginkan mas?
100% Work Om. thanks
mas kalo menggunakan jsp gimana ya codingannya?
caranya sama mencari sub total tabel..
Kalau pakai script bisa pakai loop mas, logikanya sama
mas mau tanya kalau mau menghitung total penjualan untuk 9 bulan kebelakang gimana ya makasih
Ada contoh data dan contoh output nya mas?
cntohnya di ditabel penjualan ada nama_barang, qty, total_harga, tanggal_jual,
nah mau menjumlahin total harga tapi hanya untuk 9 bulan kebelakang dari bulan sekarang
jadi outputnya pengen tau total penjualan hanya untuk 9 bulan kebelakang mas gak seluruh bulan
Coba difilter menggunakan klausa WHERE mas. Dengan asumsi format kolom tanggal_jual yyyy-mm-dd dan tipe datanya date, gunakan query ini:
Mantab
Mas kalau mau menjumlahkan tp dengan id pelanggan yang login gmn ya ? sy menggunakan framework CI
Terimakasih
Sama saja mas, tinggal diidentifikasi apakah pelanggan tersebut sudah login. biasanya mengecek bagian session. Selanjutnya jika sudah login tingga diquery berdasarkan id pelanggan tersebut
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.
Sudah coba menggunakan fungsi mail() mas? http://php.net/manual/en/function.mail.php
Pada file summary.php, susun hasil dalam bentuk HTML, mulai dari open tag HTML (<html>) hingga closing tag (</html>), kemudian kirim menggunakan fungsi mail(). Jangan lupa bagian header, bagian content-type dibuat text/html:
Tambahan:
Biasanya disetting dulu email client nya, misal dengan GMail
Jadi masalahnya ada di content-type nya ya? yang membuat pesan yang dikirim ke email berupa tulisan menjadi berderet seperti paragraf, bukan tersusun per baris.
Untuk krim email saya menggunakan phpmailer (smtp), karena kalau menggunakanfungsi mail di php tidak bisa terkirim.
Ok terima kasih untuk bantuannya.
Iya mas sama-sama…
Header penting, termasuk header untuk data yang dikirim melalui protokol HTTP, karena akan mencerminkan content yang ada seperti apa, hal ini yang menjadi dasar aplikasi client (web browser, email client, dll) mengambil keputusan apa yang harus dilakukan, seperti cara menampilkan data, mendownload content, dll
Kang, cara bikin hosting nya gimana yah ??
Banyak penyedia jasa hosting mas, bisa dicoba di googling
Halo mas, saya ingin bertanya, bagaimana menambahkan data yang sudah ada di dalam database? misalnya didalam database sudah ada barang contohnya gula pasir dengan jumlah 250kg, dan saya ingin menambahkan 100kg lagi, jadi totalnya 350kg. kira-kira scriptnya seperti apa ya?
terima kasih
Tinggal di jumlahkan saja mas, misal:
UPDATE barang SET berat = berat + 100
terima kasih banyak mas 😀
maklum masih pemula hehe
Sama sama mas…
bisa minta tolong script lengkap tuk semua tutorial diatas mas, karena saya coba sendiri dimulai dari “Menggunakan Berbagai Fungsi Agregasi” sampai “Membuat Temporary Index” GAGAL semua.. Terima kasih untuk pencerahannya
Sudah saya tambahkan link download untuk semua file sql nya mas, ada di bagian atas tutorial ini, tombol warna hijau
Gan boleh nanya ni? Caranya utk menghitung jumlah laki dan perempuan. Utk sintak mysql sama phpny gmna gan kalo di contohkan.:-) sebelumnya maaf ya gan hehehe
Ada contoh tabel data seperti apa serta contoh hasil yang diinginkan seperti apa mas?
saya punya kasus perhitungan ulangan harian,
dengan tabel :
id_ujian int(11) primary
id_mapel varchar(11)
UH1 double
UH2 double
UH3 double
UAS double
UTS double
output yang dihasilkan permapel
avg (UH 1sampai 3) = ?
UTS = ?
UAS = ?
diketahui ada 7 mapel yang berbeda
jadi, harus dirata” ulang dari setiap mapel,
avg (UH 1sampai 3) = ?
UTS = ?
UAS = ?
mohon jawaban yang terbaik ya om….
Ada contoh datanya mas? dan contoh tabel hasil yang diinginkan
Pertama:
AVG(UH 1sampai 3) = maksudnya avg antar kolom mas? kalau antar kolom kita tidak bisa menggunakan fungsi AVG, dihitung manual: ROUND((UH1 + UH2 + UH3) / 3)
UTS = ini formula menghitungnya seperti apa mas?
UAS = ini formula menghitungnya seperti apa mas?
Apa yang dimaksud seperti ini:
Kedua:
Sama seperti yang pertama, cuman perlu dihitung ratarata dari mapel, ini sepertinya ada row tersendiri untuk setiap mapel ya mas? kalau iya, gunakan fungsi AVG terlebih dahulu.
Tanpa contoh data dan formula hasil yang diinginkan seperti apa agak susah mas..
mas klo tentang travel gmna ea mas program javascrpt yg subtotal,diskon,sama totalnya mas ,hitungnya gk bisa mas
mkasih mas
Maaf belum ada versi javascriptnya mas
Mas saya tanya untuk penambahan otomatis pada tempat duduk, jadi saya mempunyai 40 tempat duduk nilai awalnya 0, jika ada yg duduk data no_kursi ditambah 1, maka ada yg dudk pertama, jika ada yg duduk lagi maka ditambah 1 sehingga nomor kursinya 2 itu contoh script nya bagaimana ya pak ?
Coba untuk kolom no_kursi nya di beri atribut AUTO_INCREMENT mas…
Halo Mas, mau tanya, bagaimana caranya untuk tahun dan id pelanggan jika ada lebih dari satu cukup dimunculkan hanya satu kali saja. Jadi kalau datanya sama tidak dimunculkan di setiap baris nya.
|2015 | 1 |100 |
101 |
Terima kasih
Coba menggunakan kluasa DISTINC mas, misal: SELECT DISTINCT… atau menggunakan GROUP BY
Bila menampilkan data di 2 table dengan qty yang sama dilokasi berbeda bagaimana ya.
Sy pnya dta
Id kode dan jumlh diambil dari table a dan lokasi jml eceran dari table b
Id kode dan jumlah diketik pengguna tapi lokasi jml eceran terisi otomatis dengan jumlah 10000 yang diketik pengguna munculah angka 5000 dan 5000 secara otomatis
Terima kasih
Maksudnya ketika user menginput id kode, otomatis keluar jumlah eceran ya mas?
kalau iya, pakai javascript atau jquery mas untuk memonitor event keyup
Assalamualaikum mas, mas saya mau tanya, saya lg belajar bikin database inventory, yg ingin saya tanyakan gimana cara ny mengurangi jumlah barang apa bila barang sedang dipinjam?jadi pada saat ada yg minjem barang stok berkurang, dan sebaliknya..mohon jwbn ny makasih
Waalaykum salam mas, bisa mas, coba pakai trigger mas, jika ada penambahan data pada tabel peminjaman, tabel barang otomatis terupdate
Maap mas bisa dicontohin ga ya cara penggunanaan ny gmana?
Sudah ada di artikelnya mas
ilmu yg bermanfaaat bagi sy..thx
Sama sama mas
Menjawab semua permasalahanku, makasih mas.
Sama sama mas
kalo mau begini gimana mas phpnya.
jadi setiap ruangan ada subtotnya mas, ditunggu ya mas via email juga boleh dianarifr@gmail.com . terima kasihz
Coba diselesaikan di SQL nya mas, menggunakan WITH ROLLUP
Mas Mau tanya aku gimana untuk penyelesaian untuk tabel seperti ini
aku ingin menghitung total jadi seperti ini
| bulan | Jumlah Tagih | Jumlah Bayar |
|2016.04 | 160000 | 160000|
yang jadi masalah cara gabungkan query nya gimana antara tabel tagih dan tabel bayar, masalah kedua tabel ( ” tanggal bayar ” ) berupa varchar dan cara konversi ke tanggal contoh ” 01-02-2016 ” bagaimana caranya… terima kasih sebelumnya mas..
Coba query ini mas:
Untuk mengubah format tanggal bisa menggunakan fungsi STR_TO_DATE mas, fungsi tersebut akan mengubah tanggal ke dalam format MySQL yyyy-dd-mm. selanjutnya untuk mengubahnya menjadi format yang diinginkan dd-mm-yyyy, gunakan fungsi DATE_FORMAT
Bagusss sekali
Common Table Expression SQL Server, pengganti Sub Query :
http://www.prihanantojoko.net/sql-common-table-expression-cte-teknik-yang-powerfull-alternatif-pengganti-sub-query/
gan, gimana cara menjumlahkan total dari subtotal pada controller codeigniter ? mohon bantuannya agan-agan sekalian. terimakasih.
Sama saja mas, tinggal dijalankan di querynya. query dan pengolahan datanya dijalankan di model
Mas mau tanya, misal data yang sudah di tampilkan dari mysql, terus di tampilkan ke dalam bentuk table,bagaimana mengambil value table tersebut mas untuk dilakukan perhitungan kembali?
Coba dipelajari artikel ini mas:
http://jagowebdev.com/menampilkan-data-mysql-dengan-php-dan-form-html/
Sederhananya seperti ini:
Oke,dicoba dulu mas
Mas masi akktif ya saya mau nanya hitungan bisa gak mas query nya saya bingung mas
Masih aktif mas, bisa mas, coba diposting disini, data awal seperti apa dan bentuk tabel akhir yang ddiinginkan seperti apa?
gan bisa kasi contoh script buat “I. Menghitung Total dan Subtotal Pada MySQL dengan PHP” di hal. web atas agan ini tapi pake mysqli.
ane ga paham script PDO.
ato agan buat contoh yang simple aja. dengan mysqli / pdo ato yang lain, jadi pengunjung web agan banyak pilihan.
untuk mysqli tinggal gunakan fungsi mysqli_query(), coba seperti ini mas:
$sql = ‘SELECT…’;
$query = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($query)) {
echo $row[‘nama_kolom’]
}
mas agus.. kalo yang output nya pertahun kodenya bagaimana?
saya ingin menjumlah tahun 2015 nya saja.
ada 4 kolom:
tinggal di group by id_user sama wherenya di filter tahun 2015 mas, kira kira seperti ini:
Mas, minta tolong masukkan database nya dimana??
kan hanya dimasukkan tabel nya tapi database nya tidak ada
maan masih pemula
Maksudnya bagaimana ya mas? coba di download file nya mas ada di tombol Download di artikel sebelah atas
mas.. saya mau bertanya nih. ada kasus perhitungan ulangan harian siswa untuk menghitung jawaban nya secara otomatis dengan meng-input data.
cara kerja nya begini :
1. input pertama kunci jawaban di sebuah kolom atau tabel vertikal
2. input jawaban dari siswa di kolom vertikal disamping kolom kunci jawaban.
3. hitung jawaban yang benar berapa.
4. bentuk dari jawaban siswanya multiple choice.
5. langsung kelihatan berapa benar dan salahnya mas
gimana cara buatnya ya mas..
mohon bantuan nya
Untuk data permanen, kunci jawabannya dan jawaban siswa bisa dissimpan dulu di database mas.
Sebaiknya di buat tabel terpisah, misal:
Tabel kunci_jawaban
soal_no jawaban
1 A
2 C
…
jawaban_siswa
kd_siswa no_soal jawaban
A12 1 A
A12 2 D
…
Untuk menghitung jawaban yang benar, dengan SQL bandingkan kunci dan jawaban, beri nilai 1 untuk jawaban benar dan NULL untuk jawaban salah, selanjutnya hitung yang benar dan yang salah menggunakan fungsi SUM atau COUNT
Selamat sore Pak, saya ada kasus report seperti ini :
Mohon pencerahan nya. Terima kasih
Leo
Itu mau diapain ya mas?
Pak Agus, bolehkan sy kirim email.?
Iya silakan mas…
email nya apa ya mas??
Ada di halaman contact mas…
mas mau nanya script phpnya donk tolong di bantu ya
jadi kan gini, pada web yang saya mau bikin itu, ketika kita mengklik nama barang, muncul otomatis jenis, satuan, harga minggu1, minggu2, minggu3, minggu4, dan minggu5, nah yang saya mau tanyakan adalah
script untuk hitung otomatis buat harga rata-rata dari harga minggu1, minggu2, minggu3, minggu4, dan minggu5, itu gmna mas, harga minggu ke 1 – ke 5 nya sdh muncul secara otomatis dari pemilihan nama barang tadi tinggal memunculkan harga rata-ratanya aja lagi yang kurang paham, terimakasih.
Bisa langsung dari SQL atau dari PHP mas, tinggal ditambahkan minggu 1 s.d 5 terus dibagi 5
SQL:
PHP:
Mas Bisa bantu tentang penjumlahan di simpan koperasi
misalnya saya punya tabel 3 yaitu anggota,jenis,simpanan
nah yang jenis sama anggota itu ngerelasi ke tabel simpanan
isi tabel simpanan yakni id,id_anggota,id_jns_simpanan,jumlah_simpan,total simpan
tolong bantu ya mas
Contoh bentuk tabel nya seperti apa mas? dan outputnya seperti apa?
sangat membantu mas (Y) terima kasih ..
Sama sama mas…
mas.. saya boleh nanya gak mas,,,saya mau menjumlahkan data saya
Bentuk tabel nya seperti apa ya mas? Sama output yang diinginkan seperti apa?
mas agus.. mau nanya nih..
saya punya tabel penjualan.. dengan datanya ada 15 record dalam bulan Januari 2018. Kalau saya panggil untuk ditampilkan dalam bentuk tabel kan cuma ada 15 tanggal dengan data penjualan.
pertanyaannya:
Saya ingin menampilkan tabel dengan tampilan tabel bulan januari 2018 dengan data tgl 01 s/d 31(akhir bulan).
Untuk kolom tanggal, tampil dari tgl. 01 s/d 31 dan kolom penjualan terisi dengan jmlh penjualan sesuai inputan, sedang yang tidak ada inputan jumlah penjualannya akan terisi 0.
mohon pencerahaanya dengan contoh skrip mas..
terimakasih sebelumnya.
Coba ambil data seperti biasa menggunakan query dan hasilnya disimpan ke dalam array, kemudian untuk tanggalnya ditampilkan semua dengan php, selanjutnya di cek di array tersebut apakah di tanggal tersebut ada datanya, misal:
mas ,,
saya mau tanya ..
gimana menampilkan subtotal di posisi atas per tahun ?
terimakasih ..
Untuk display bisa disiasati di level presentasinya mas, misal dengan php, sebelum di tampilkan datanya, di tampilkan dulu totalnya, atau dengan query MySQL bisa menggunakan union dengan query totalnya diletakkan di atas.
Assalamualaikum, mas mohon bantuannya,
saya punya tabel kabkot seperti ini :
Iya mas…
boleh minta contohnya mas menggunakan php,,
karena nilai subtotal pertama nilainya kosong, sedangkan subtotal kedua menampilkan nilai subtotal pertama,,
Pokok;
$rupiah = number_format($nominal, “0”, “,”, “.”);
echo $rupiah; ?>
Jika dibut dari sisi aplikasi, memang harus di loop dulu hasilnya untuk mendapatkan jumlah total datannya mas:
Agar lebih efisien, total data bisa dibuat menggunakan fungsi SUM pada sql
terimakasih mas ..
untuk proses penjumlahannya sudah bisa
Sama sama mbak…
Selamat malem mas, mau tanya bagaimana cara menjumlahkan sebuah kolom yang ditampilkan menggunakan do…while…supaya hasil yang ditampilkan sesuai jumlah baris yang ditampilkan, bukan jumlah keseluruhan baris?
misal ada 10 data, tiap data memiliki nilai 10 jadi jumlah keseluruhan adalah 10×10=100, tapi masalahnya saya mau menampilkan 5 data saja, sisanya saya tampilkan dihalaman berikutnya, jadi halaman pertama jumlahnya harusnya 50 dari total jumlah 100, caranya kira2 gimana mas? semoga bisa dipahami pertanyaannya ya mas, bingung jelasinnya hehehe….thanks b4
Oh… itu pakai limit di SQL nya mas, limit nya bisanya ngikut url, misal:
http://localhost/data.php
LIMIT 0, 50
http://localhost/data.php?page=2
LIMIT 50, 100
dst….
malam mas, saya ada beberapa pertanyaan.. kiranya sudi membantu.
saya punya database seperti dibawah ini:
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.
Script PHP nya panjang mas:
1. Untuk membuat combobox dari database, bisa mempelajari artikel ini: JSON Pada Javascript – Panduan Lengkap Demonya: Javascript Combo Dynamic
2. Maksudnya Input box dengan autocomplete ya mas? itu perlu javascript untuk menangkap event keyup mas, sesaat setelah keyboard selesai dipencet, selanjutnya digabungkan dengan AJAX ke halaman php yang berisi prediksi kata. Halaman php tersebut melakukan query ke database.
Namun biasanya isi lemot mas, lebih efektif jika data tersebut sudah diambil terlebih dahulu dari database kemudian disimpan pada variabel javascrit kemudian baru ditampilkan ketika terjadi event keyup
3. Untuk perhitungan otomatis ini juga perlu Javascript mas, menggunakan event keyup juga, jadi sesaat setelah selesai keyboard diketik, script melakukan penghitungan dari input asal dan tujuan
Semoga dapat membantu…