KAL
Menu

WITH ROLLUP

Menambahkan total dan subtotal pada hasil GROUP BY secara otomatis.

Daftar isi

Topik ini membahas modifier WITH ROLLUP yang menambahkan baris subtotal dan total pada hasil pengelompokan tanpa query tambahan. Pembahasan mencakup pengelompokan dua tingkat sebagai pembanding, makna baris NULL pada hasil rollup, penandaan baris subtotal dengan fungsi GROUPING(), serta pengurutan dan pembatasan hasilnya. Basis data latihan berupa catatan pengukuran suhu dibangun ulang dari berkas skrip, sehingga seluruh keluaran pada contoh dapat diulang dari data yang sama.

Menyiapkan basis data latihan#

Basis data latihan sensor_suhu dibangun ulang dari berkas skrip agar seluruh contoh pada topik ini bermula dari data yang sama. Berkas data-sensor-suhu.sql memuat pembuatan ulang basis data, tabel pengukuran, dan dua belas baris data. Data meniru log sensor suhu, yaitu dua sensor bernama S1 dan S2 yang masing-masing mencatat tiga pembacaan pada tanggal 2025-06-01 dan tiga pembacaan pada tanggal 2025-06-02. Kolom suhu bertipe DECIMAL(4,1) sehingga nilai rata-rata pada keluaran nanti dapat diperiksa ke satu angka di belakang komanya.

sql data-sensor-suhu.sql 
-- tidak-dijalankan
-- Berkas latihan topik WITH ROLLUP: membangun ulang basis data sensor_suhu dari nol.
-- Jalankan dengan: mysql -u root < data-sensor-suhu.sql

DROP DATABASE IF EXISTS sensor_suhu;
CREATE DATABASE sensor_suhu;
USE sensor_suhu;

CREATE TABLE pengukuran (
  id INT PRIMARY KEY,
  sensor VARCHAR(2) NOT NULL,
  tanggal DATE NOT NULL,
  suhu DECIMAL(4,1) NOT NULL
);

INSERT INTO pengukuran (id, sensor, tanggal, suhu) VALUES
  (1,  'S1', '2025-06-01', 22.0),
  (2,  'S1', '2025-06-01', 23.0),
  (3,  'S1', '2025-06-01', 24.0),
  (4,  'S1', '2025-06-02', 23.5),
  (5,  'S1', '2025-06-02', 24.5),
  (6,  'S1', '2025-06-02', 22.5),
  (7,  'S2', '2025-06-01', 21.0),
  (8,  'S2', '2025-06-01', 22.0),
  (9,  'S2', '2025-06-01', 23.0),
  (10, 'S2', '2025-06-02', 24.0),
  (11, 'S2', '2025-06-02', 25.0),
  (12, 'S2', '2025-06-02', 23.0);
bash 
# membangun ulang basis data latihan dari berkas skrip
mysql -u root < data-sensor-suhu.sql

Perintah di atas tidak mencetak apa pun karena skripnya hanya berisi pernyataan pembuatan struktur dan pengisian data. Tabel pengukuran kini menjadi bahan untuk mengamati bagaimana subtotal dan total terbentuk secara otomatis. Seluruh kolomnya bertanda NOT NULL, sehingga tidak ada NULL sungguhan pada data dan baris NULL pada keluaran nanti pasti baris rollup. Nilai suhu pada data berada pada rentang wajar ruangan, yaitu 21.0 sampai 25.0. Latihan juga dapat diulang kapan pun karena berkas skripnya selalu memulai dari keadaan kosong.

Pengelompokan dua tingkat tanpa subtotal#

Klausa GROUP BY dengan dua kolom membentuk kelompok untuk setiap kombinasi nilai keduanya. Query berikut merangkum banyaknya pembacaan dan rata-rata suhu untuk setiap pasangan sensor dan tanggal.

bash 
# merangkum suhu per sensor dan per tanggal
mysql -t -u root sensor_suhu -e "SELECT sensor, tanggal, COUNT(*) AS jumlah, AVG(suhu) AS rata_rata FROM pengukuran GROUP BY sensor, tanggal ORDER BY sensor, tanggal;"
text Keluaran 
+--------+------------+--------+-----------+
| sensor | tanggal    | jumlah | rata_rata |
+--------+------------+--------+-----------+
| S1     | 2025-06-01 |      3 |  23.00000 |
| S1     | 2025-06-02 |      3 |  23.50000 |
| S2     | 2025-06-01 |      3 |  22.00000 |
| S2     | 2025-06-02 |      3 |  24.00000 |
+--------+------------+--------+-----------+

Keluaran memuat empat kelompok yang masing-masing berisi tiga pembacaan, dan urutannya ditetapkan oleh ORDER BY pada kedua kolom kelompok agar deterministik. Rekap ini belum menjawab pertanyaan tentang total per sensor maupun total keseluruhan. Alternatif tanpa WITH ROLLUP adalah menjalankan GROUP BY sensor untuk subtotal dan satu query agregat tanpa kelompok untuk total, lalu menyusunnya kembali di sisi aplikasi. Pendekatan itu menggandakan jumlah query dan menyatukan hasil yang bentuk barisnya berbeda, sehingga rawan tidak konsisten apabila salah satu querynya diperbarui tanpa yang lain. Modifier WITH ROLLUP menyelesaikan seluruh kebutuhan itu dalam satu query, dan keluaran empat kelompok di atas menjadi acuan untuk memeriksa kebenaran angka subtotal pada bagian berikutnya.

Menambahkan subtotal dengan WITH ROLLUP#

Modifier WITH ROLLUP ditempatkan setelah daftar kolom GROUP BY dan menambahkan baris agregat untuk setiap tingkat pengelompokan. Baris tambahan ini disebut baris super-agregat (super-aggregate row) dalam dokumentasi resmi MySQL, yaitu baris yang angkanya dihitung dari seluruh kelompok pada tingkat di bawahnya.

bash 
# menambahkan subtotal per sensor dan total keseluruhan
mysql -t -u root sensor_suhu -e "SELECT sensor, tanggal, COUNT(*) AS jumlah, AVG(suhu) AS rata_rata FROM pengukuran GROUP BY sensor, tanggal WITH ROLLUP;"
text Keluaran 
+--------+------------+--------+-----------+
| sensor | tanggal    | jumlah | rata_rata |
+--------+------------+--------+-----------+
| S1     | 2025-06-01 |      3 |  23.00000 |
| S1     | 2025-06-02 |      3 |  23.50000 |
| S1     | NULL       |      6 |  23.25000 |
| S2     | 2025-06-01 |      3 |  22.00000 |
| S2     | 2025-06-02 |      3 |  24.00000 |
| S2     | NULL       |      6 |  23.00000 |
| NULL   | NULL       |     12 |  23.12500 |
+--------+------------+--------+-----------+

Baris S1 dengan tanggal NULL adalah subtotal sensor S1, yaitu enam pembacaan dengan rata-rata 23.25, dan baris serupa berlaku untuk S2. Baris paling akhir dengan sensor dan tanggal sama-sama NULL adalah total keseluruhan atas dua belas pembacaan. Angka pada baris super-agregat dihitung ulang dari baris dasar, bukan diolah dari angka kelompok anaknya, sehingga rata-rata total 23.125 dihitung langsung dari dua belas nilai suhu. Perlu dicatat pula bahwa baris super-agregat tidak menggantikan baris kelompok biasa, sehingga panjang keluaran bertambah tiga baris pada contoh ini, yaitu dua subtotal dan satu total. Penempatan barisnya adalah perilaku bawaan server, yaitu subtotal tepat setelah kelompoknya dan total paling akhir, dan penempatan ini stabil untuk query yang sama pada server yang sama.

Menandai baris rollup dengan GROUPING()#

Nilai NULL pada baris rollup berarti seluruh nilai tingkat tersebut, tetapi bentuknya identik dengan NULL dari data biasa. Untuk laporan, baris tersebut biasanya diberi label agar pembacanya tidak mengira data itu hilang. Fungsi IFNULL menggantikan NULL dengan teks pilihan, dan hasilnya lebih mudah dibaca.

bash 
# memberi label pada baris subtotal dan total
mysql -t -u root sensor_suhu -e "SELECT IFNULL(sensor, 'SEMUA SENSOR') AS sensor, IFNULL(tanggal, 'SEMUA TANGGAL') AS tanggal, COUNT(*) AS jumlah FROM pengukuran GROUP BY sensor, tanggal WITH ROLLUP;"
text Keluaran 
+--------------+---------------+--------+
| sensor       | tanggal       | jumlah |
+--------------+---------------+--------+
| S1           | 2025-06-01    |      3 |
| S1           | 2025-06-02    |      3 |
| S1           | SEMUA TANGGAL |      6 |
| S2           | 2025-06-01    |      3 |
| S2           | 2025-06-02    |      3 |
| S2           | SEMUA TANGGAL |      6 |
| SEMUA SENSOR | SEMUA TANGGAL |     12 |
+--------------+---------------+--------+

Fungsi GROUPING() mengembalikan 1 apabila nilai kolomnya berasal dari baris rollup dan 0 apabila berasal dari baris kelompok biasa. Fungsi ini menjadi pembeda yang pasti antara NULL data dan NULL penanda total.

bash 
# membedakan baris rollup dengan GROUPING()
mysql -t -u root sensor_suhu -e "SELECT sensor, tanggal, GROUPING(sensor) AS grup_sensor, GROUPING(tanggal) AS grup_tanggal, COUNT(*) AS jumlah FROM pengukuran GROUP BY sensor, tanggal WITH ROLLUP;"
text Keluaran 
+--------+------------+-------------+--------------+--------+
| sensor | tanggal    | grup_sensor | grup_tanggal | jumlah |
+--------+------------+-------------+--------------+--------+
| S1     | 2025-06-01 |           0 |            0 |      3 |
| S1     | 2025-06-02 |           0 |            0 |      3 |
| S1     | NULL       |           0 |            1 |      6 |
| S2     | 2025-06-01 |           0 |            0 |      3 |
| S2     | 2025-06-02 |           0 |            0 |      3 |
| S2     | NULL       |           0 |            1 |      6 |
| NULL   | NULL       |           1 |            1 |     12 |
+--------+------------+-------------+--------------+--------+

Keluaran memperlihatkan pola tegas, yaitu 0 dan 0 untuk baris kelompok, 0 dan 1 untuk subtotal sensor, serta 1 dan 1 untuk total keseluruhan. Argumen GROUPING() wajib berupa kolom yang muncul pada GROUP BY, dan pelanggarannya ditolak server.

bash 
# sengaja memanggil GROUPING() dengan kolom di luar GROUP BY
mysql -t -u root sensor_suhu -e "SELECT GROUPING(id) FROM pengukuran GROUP BY sensor WITH ROLLUP;"
text Keluaran 
ERROR 3602 (HY000) at line 1: Argument #1 of GROUPING function is not in GROUP BY

Mengurutkan dan membatasi hasil rollup#

Tanpa ORDER BY, server menempatkan baris super-agregat sesuai tingkatnya sebagaimana keluaran sebelumnya. Server MySQL 8.0 yang dipakai di jalur ini memperbolehkan ORDER BY bersama WITH ROLLUP, dan urutan NULL mengikuti aturan umum pengurutan MySQL, yaitu NULL muncul lebih dahulu pada urutan naik dan lebih akhir pada urutan turun.

bash 
# mengurutkan hasil rollup menurun per sensor
mysql -t -u root sensor_suhu -e "SELECT sensor, tanggal, COUNT(*) AS jumlah FROM pengukuran GROUP BY sensor, tanggal WITH ROLLUP ORDER BY sensor DESC, tanggal;"
text Keluaran 
+--------+------------+--------+
| sensor | tanggal    | jumlah |
+--------+------------+--------+
| S2     | NULL       |      6 |
| S2     | 2025-06-01 |      3 |
| S2     | 2025-06-02 |      3 |
| S1     | NULL       |      6 |
| S1     | 2025-06-01 |      3 |
| S1     | 2025-06-02 |      3 |
| NULL   | NULL       |     12 |
+--------+------------+--------+

Keluaran memperlihatkan sensor menurun dari S2 ke S1, sementara subtotal masing-masing sensor naik ke posisi teratas karena tanggal-nya NULL pada urutan naik. Total keseluruhan berpindah ke baris akhir karena nilai sensor-nya NULL pada urutan turun. Klausa LIMIT juga sah digunakan, tetapi pembatasannya bekerja pada baris keluaran tanpa memedulikan status rollup.

bash 
# membatasi hasil rollup dengan LIMIT
mysql -t -u root sensor_suhu -e "SELECT sensor, tanggal, COUNT(*) AS jumlah FROM pengukuran GROUP BY sensor, tanggal WITH ROLLUP LIMIT 3;"
text Keluaran 
+--------+------------+--------+
| sensor | tanggal    | jumlah |
+--------+------------+--------+
| S1     | 2025-06-01 |      3 |
| S1     | 2025-06-02 |      3 |
| S1     | NULL       |      6 |
+--------+------------+--------+

Keluaran hanya memuat tiga baris pertama untuk sensor S1, sehingga data S2 dan total keseluruhan terpotong. Karena itu, hindari LIMIT pada laporan rollup apabila baris subtotal dan total harus tampil lengkap, atau pastikan jumlah baris yang diminta mencakup semuanya. Urutan keluaran pada kedua contoh ini memang stabil untuk data dan server yang sama, tetapi posisi baris rollup tetap harus dibaca dari nilai NULL-nya, bukan dari kebiasaan bahwa pengelompokan selalu mengurutkan.

Kesalahan umum#

Kesalahan pertama adalah membaca baris rollup sebagai data. Baris dengan tanggal NULL bukan pembacaan yang hilang, melainkan subtotal, dan kesalahan pembacaan ini berbahaya saat hasil rollup digabung dengan data lain. Pembeda yang aman adalah GROUPING(), sedangkan IFNULL hanya tepat pada data yang dijamin bebas NULL. Periksa dahulu kebersihan data sebelum memilih cara pelabelannya.

Kesalahan kedua adalah menganggap angka subtotal sebagai pengolahan ulang angka kelompok anaknya. Rata-rata subtotal dihitung dari baris dasar, sehingga hasilnya cocok dengan rata-rata dari rata-rata kelompok apabila semua kelompok sama besar, dan berbeda begitu ukuran kelompoknya tidak sama. Angka subtotal pada kolom COUNT dan SUM juga bukan hasil penghitungan ulang, melainkan agregasi langsung atas baris dasar.

Kesalahan ketiga adalah menerapkan HAVING tanpa menyadari dampaknya pada baris rollup. Syarat seperti HAVING COUNT(*) < 6 menghapus subtotal dan total karena keduanya berjumlah 6 dan 12, sehingga laporan kehilangan baris rangkumannya tanpa pesan apa pun. Rancang syarat HAVING dengan memperhitungkan baris super-agregat.

Ringkasan#

  • WITH ROLLUP menambahkan baris super-agregat untuk setiap tingkat GROUP BY, yaitu subtotal per kelompok diikuti total keseluruhan.
  • Nilai NULL pada baris rollup berarti seluruh nilai tingkat tersebut, dan GROUPING() membedakannya dari NULL data secara pasti.
  • Angka pada baris super-agregat dihitung ulang dari baris dasar, bukan dari angka kelompok anaknya.
  • ORDER BY dan LIMIT sah dipakai pada server MySQL 8.0, tetapi keduanya bekerja tanpa memedulikan status rollup sehingga urutan dan potongan keluarannya perlu diperiksa.

Referensi#