KAL
Menu

Grouping Set

Subtotal dan grand total dengan pola UNION ALL yang idiomatik untuk SQLite.

Daftar isi

Laporan bertingkat sering menuntut baris rinci, baris subtotal, dan baris grand total dalam satu keluaran. SQLite tidak menyediakan klausa ROLLUP maupun GROUPING SETS bawaan, sehingga pola yang idiomatik adalah menggabungkan beberapa query ber-GROUP BY dengan UNION ALL. Topik ini membangun pola tersebut bertahap, mulai dari grand total sederhana sampai subtotal dua dimensi, disertai cara melabeli baris total agar laporan tidak menyesatkan. Dataset peminjaman dan anggota yang sama dipakai, dan setiap blok SQL memuat pembuatan tabelnya sendiri sehingga dapat dijalankan langsung dengan sqlite3 :memory:.

Titik berangkat laporan bertingkat#

Query pengelompokan biasa menghasilkan baris rinci per kelompok tanpa baris total. Query berikut menghitung jumlah peminjaman dan total denda per anggota sebagai dasar laporan yang akan dilengkapi bertahap.

sql Baris rinci per anggota 
CREATE TABLE anggota (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL
);
CREATE TABLE peminjaman (
  id INTEGER PRIMARY KEY,
  id_anggota INTEGER,
  id_buku INTEGER,
  tanggal_pinjam TEXT,
  denda REAL
);
INSERT INTO anggota (id, nama) VALUES
  (1, 'Ali'), (2, 'Budi'), (3, 'Citra'),
  (4, 'Dewi'), (5, 'Eka'), (6, 'Farhan');
INSERT INTO peminjaman (id, id_anggota, id_buku, tanggal_pinjam, denda) VALUES
  (1, 1, 1, '2026-03-02', 0.5),
  (2, 1, 3, '2026-03-04', 1.0),
  (3, 2, 2, '2026-03-05', NULL),
  (4, 3, 1, '2026-03-09', 0.5),
  (5, 3, 4, '2026-03-12', 2.0),
  (6, 4, 5, '2026-03-16', NULL),
  (7, 5, 3, '2026-03-19', 1.0),
  (8, 5, 6, '2026-03-23', NULL);

SELECT a.nama, COUNT(*) AS jumlah, SUM(p.denda) AS total_denda
FROM peminjaman p
JOIN anggota a ON a.id = p.id_anggota
GROUP BY a.nama
ORDER BY a.nama;
text Keluaran 
Ali|2|1.5
Budi|1|
Citra|2|2.5
Dewi|1|
Eka|2|1.0

Lima baris tersebut menjawab pertanyaan per anggota, tetapi pembaca laporan biasanya juga bertanya berapa total keseluruhannya. Menjawabnya dengan query kedua yang dijalankan terpisah berarti keluaran laporan berada di dua tempat yang berbeda dan mudah tidak sinkron. Pola yang lebih baik menyatukan baris rinci dan baris total dalam satu keluaran yang urutannya terjaga.

Pola UNION ALL untuk grand total#

Pola dasarnya menggabungkan dua SELECT yang bentuk kolomnya sama. SELECT pertama menghasilkan baris rinci per kelompok, sedangkan SELECT kedua menghasilkan satu baris total atas seluruh tabel. Kolom literal tingkat dan kelompok ditambahkan pada setiap cabang agar kedua hasil memiliki bentuk yang sama dan dapat dibedakan asalnya.

sql Baris rinci dan grand total dengan UNION ALL 
CREATE TABLE anggota (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL
);
CREATE TABLE peminjaman (
  id INTEGER PRIMARY KEY,
  id_anggota INTEGER,
  id_buku INTEGER,
  tanggal_pinjam TEXT,
  denda REAL
);
INSERT INTO anggota (id, nama) VALUES
  (1, 'Ali'), (2, 'Budi'), (3, 'Citra'),
  (4, 'Dewi'), (5, 'Eka'), (6, 'Farhan');
INSERT INTO peminjaman (id, id_anggota, id_buku, tanggal_pinjam, denda) VALUES
  (1, 1, 1, '2026-03-02', 0.5),
  (2, 1, 3, '2026-03-04', 1.0),
  (3, 2, 2, '2026-03-05', NULL),
  (4, 3, 1, '2026-03-09', 0.5),
  (5, 3, 4, '2026-03-12', 2.0),
  (6, 4, 5, '2026-03-16', NULL),
  (7, 5, 3, '2026-03-19', 1.0),
  (8, 5, 6, '2026-03-23', NULL);

SELECT 'per anggota' AS tingkat, a.nama AS kelompok, COUNT(*) AS jumlah, SUM(p.denda) AS total_denda
FROM peminjaman p
JOIN anggota a ON a.id = p.id_anggota
GROUP BY a.nama
UNION ALL
SELECT 'total', 'Semua', COUNT(*), SUM(denda)
FROM peminjaman
ORDER BY tingkat, kelompok;
text Keluaran 
per anggota|Ali|2|1.5
per anggota|Budi|1|
per anggota|Citra|2|2.5
per anggota|Dewi|1|
per anggota|Eka|2|1.0
total|Semua|8|5.0

UNION ALL menyusun hasil kedua query tanpa membuang baris yang sama, dan ORDER BY di akhir mengurutkan seluruh gabungan. Syarat bentuknya ketat, yaitu jumlah kolom setiap cabang harus sama dan tipenya serasi, sedangkan nama kolom diambil dari cabang pertama. Kolom tingkat menjadi kunci urutan utama sehingga baris total selalu tampil di akhir, dan kolom kelompok menjaga urutan abjad di dalam tingkatnya. Letakkan ORDER BY sekali di akhir gabungan, bukan di dalam setiap cabang, karena urutan yang dinyatakan di dalam cabang tidak menular ke keluaran akhir.

Memberi label baris total#

Baris total yang berlabel Semua jelas bagi pembacanya. Pilihan lain adalah menaruh NULL sebagai label di cabang total, lalu menampilkannya kembali melalui COALESCE pada query pembungkus. Pendekatan ini berguna ketika teks label ditentukan oleh program pembaca hasil, bukan oleh SQL-nya, karena sentinel NULL lebih mudah dikenali daripada teks yang harus dicocokkan.

sql Sentinel NULL pada label yang ditampilkan ulang 
CREATE TABLE anggota (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL
);
CREATE TABLE peminjaman (
  id INTEGER PRIMARY KEY,
  id_anggota INTEGER,
  id_buku INTEGER,
  tanggal_pinjam TEXT,
  denda REAL
);
INSERT INTO anggota (id, nama) VALUES
  (1, 'Ali'), (2, 'Budi'), (3, 'Citra'),
  (4, 'Dewi'), (5, 'Eka'), (6, 'Farhan');
INSERT INTO peminjaman (id, id_anggota, id_buku, tanggal_pinjam, denda) VALUES
  (1, 1, 1, '2026-03-02', 0.5),
  (2, 1, 3, '2026-03-04', 1.0),
  (3, 2, 2, '2026-03-05', NULL),
  (4, 3, 1, '2026-03-09', 0.5),
  (5, 3, 4, '2026-03-12', 2.0),
  (6, 4, 5, '2026-03-16', NULL),
  (7, 5, 3, '2026-03-19', 1.0),
  (8, 5, 6, '2026-03-23', NULL);

SELECT tingkat, COALESCE(kelompok, 'Semua') AS kelompok, jumlah, total_denda
FROM (
  SELECT 'per anggota' AS tingkat, a.nama AS kelompok, COUNT(*) AS jumlah, SUM(p.denda) AS total_denda
  FROM peminjaman p
  JOIN anggota a ON a.id = p.id_anggota
  GROUP BY a.nama
  UNION ALL
  SELECT 'total', NULL, COUNT(*), SUM(denda)
  FROM peminjaman
)
ORDER BY tingkat, kelompok;
text Keluaran 
per anggota|Ali|2|1.5
per anggota|Budi|1|
per anggota|Citra|2|2.5
per anggota|Dewi|1|
per anggota|Eka|2|1.0
total|Semua|8|5.0

Keluarannya identik dengan versi sebelumnya karena NULL diganti Semua saat dibaca. Perbedaannya ada pada struktur, yaitu subquery menyimpan sentinel NULL, sedangkan COALESCE mengubahnya menjadi teks pada lapisan terluar. Kolom tingkat tetap menjadi kunci urutan pertama sehingga baris total tetap tampil di akhir, dan kedua gaya label dapat dipilih sesuai kebutuhan program pembaca hasilnya.

Subtotal pada lebih dari satu dimensi#

Laporan bertingkat yang sesungguhnya biasanya memuat lebih dari satu dimensi. Pola yang sama meluas dengan menambahkan cabang baru untuk setiap himpunan pengelompokan, dan setiap cabang itulah yang oleh dokumen standar disebut grouping set. Query berikut menyusun tiga cabang, yaitu rinci per anggota, subtotal per buku, dan grand total.

sql Tiga grouping set dalam satu laporan 
CREATE TABLE anggota (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL
);
CREATE TABLE peminjaman (
  id INTEGER PRIMARY KEY,
  id_anggota INTEGER,
  id_buku INTEGER,
  tanggal_pinjam TEXT,
  denda REAL
);
INSERT INTO anggota (id, nama) VALUES
  (1, 'Ali'), (2, 'Budi'), (3, 'Citra'),
  (4, 'Dewi'), (5, 'Eka'), (6, 'Farhan');
INSERT INTO peminjaman (id, id_anggota, id_buku, tanggal_pinjam, denda) VALUES
  (1, 1, 1, '2026-03-02', 0.5),
  (2, 1, 3, '2026-03-04', 1.0),
  (3, 2, 2, '2026-03-05', NULL),
  (4, 3, 1, '2026-03-09', 0.5),
  (5, 3, 4, '2026-03-12', 2.0),
  (6, 4, 5, '2026-03-16', NULL),
  (7, 5, 3, '2026-03-19', 1.0),
  (8, 5, 6, '2026-03-23', NULL);

SELECT 'per anggota' AS tingkat, a.nama AS kelompok, COUNT(*) AS jumlah, SUM(p.denda) AS total_denda
FROM peminjaman p
JOIN anggota a ON a.id = p.id_anggota
GROUP BY a.nama
UNION ALL
SELECT 'per buku', 'buku ' || p.id_buku, COUNT(*), SUM(p.denda)
FROM peminjaman p
GROUP BY p.id_buku
UNION ALL
SELECT 'total', 'Semua', COUNT(*), SUM(denda)
FROM peminjaman
ORDER BY tingkat, kelompok;
text Keluaran 
per anggota|Ali|2|1.5
per anggota|Budi|1|
per anggota|Citra|2|2.5
per anggota|Dewi|1|
per anggota|Eka|2|1.0
per buku|buku 1|2|1.0
per buku|buku 2|1|
per buku|buku 3|2|2.0
per buku|buku 4|1|2.0
per buku|buku 5|1|
per buku|buku 6|1|
total|Semua|8|5.0

Tiga cabang menghasilkan dua belas baris yang tersusun rapi. Enam baris per buku menunjukkan total denda masing-masing koleksi, misalnya buku 3 dengan dua peminjaman dan total denda 2.0. Grand total 5.0 pada baris terakhir cocok dengan penjumlahan seluruh cabang rinci, dan kecocokan itu menjadi pemeriksaan cepat bahwa pola UNION ALL disusun dengan benar. Menambahkan dimensi ketiga berarti menambahkan satu cabang lagi dengan bentuk kolom yang sama, sedangkan urutan tingkatnya naik secara otomatis melalui ORDER BY.

Perbandingan singkat dengan ROLLUP#

Mesin basis data lain menyediakan jalan pintas untuk pola di atas. PostgreSQL dan MySQL menawarkan klausa ROLLUP yang menghasilkan baris rinci, subtotal, dan grand total dari satu GROUP BY tanpa UNION ALL. Bentuknya seperti berikut, dan blok ini tidak dijalankan karena SQLite tidak mendukung sintaksnya.

sql Bentuk ROLLUP yang tidak dimiliki SQLite 
-- tidak-dijalankan
-- sintaks ROLLUP pada PostgreSQL dan MySQL
SELECT a.nama, SUM(p.denda)
FROM peminjaman p
JOIN anggota a ON a.id = p.id_anggota
GROUP BY ROLLUP (a.nama);

Pembandingan singkatnya sebagai berikut. ROLLUP lebih ringkas dan menghitung agregat sekali jalan, sedangkan pola UNION ALL lebih bertele-tele tetapi memberi kendali penuh atas label, urutan, dan bentuk setiap tingkat. SQLite tidak menyediakan ROLLUP maupun GROUPING SETS, sehingga pola manual itulah yang idiomatik pada jalur ini. Kelebihan tambahannya, pola yang sama berjalan tanpa perubahan pada mesin yang mendukung ROLLUP, sehingga query laporan tetap portabel.

Kesalahan umum#

Jumlah atau tipe kolom cabang yang tidak serasi. UNION ALL menuntut jumlah kolom yang sama pada setiap cabang, dan ketidaksamaan tipe membuat angka tampil sebagai teks. Selaraskan daftar kolom dan beri kolom literal tingkat serta kelompok pada setiap cabang.

ORDER BY di dalam setiap cabang. Urutan hanya dijaga bila ORDER BY diletakkan sekali di akhir gabungan, sehingga ORDER BY pada cabang tidak memberi jaminan apa pun pada keluaran akhir. Pindahkan semua syarat urutan ke ORDER BY yang berada di akhir gabungan.

Baris total ikut terhitung pada agregat cabang lain. Pola UNION ALL menggabungkan hasil query yang sudah selesai, tetapi kesalahan menyalin hasil laporan termasuk baris totalnya ke tabel kerja lalu diagregasi ulang akan menggandakan angka. Pastikan setiap agregasi hanya membaca data mentah.

Ringkasan#

  • SQLite tidak memiliki ROLLUP maupun GROUPING SETS, dan subtotal disusun dengan pola UNION ALL atas beberapa query ber-GROUP BY.
  • Setiap cabang wajib berbentuk kolom yang sama, dan kolom literal tingkat beserta kelompok menjaga baris total tetap terbedakan.
  • ORDER BY diletakkan sekali di akhir gabungan, biasanya pada kolom tingkat lalu kelompok, agar urutan baca terjaga.
  • Label baris total diberikan melalui teks Semua atau sentinel NULL yang ditampilkan ulang dengan COALESCE.

Referensi#

  • SELECT — SQLite — Dokumentasi resmi compound SELECT dengan UNION ALL serta klausa GROUP BY dan ORDER BY yang menjadi bahan pola subtotal.
  • SQLite Documentation — SQLite — Indeks dokumentasi resmi untuk menelusuri klausul pemilihan data lebih dalam.