Fungsi Agregat SQL di SQLite: COUNT, SUM, AVG, MIN, MAX

Apa yang Sebenarnya Dilakukan Fungsi Agregat

Sebagian besar fungsi SQL yang sudah kamu temui sejauh ini bekerja baris demi baris: UPPER(name) dijalankan sekali per baris, ROUND(price, 2) juga dijalankan sekali per baris. Fungsi agregat berbeda. Fungsi ini melihat satu kumpulan baris sekaligus lalu meringkasnya menjadi satu nilai.

Siapkan tabel kecil untuk dicoba:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada',   42.00, 'paid'),
    ('Boris', 15.50, 'paid'),
    ('Ada',   88.00, 'paid'),
    ('Carl',  10.00, 'refunded'),
    ('Ada',   NULL,  'pending');

SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

Lima baris masuk, satu baris keluar. Itulah gambaran besarnya: fungsi agregat memadatkan baris menjadi ringkasan. Tanpa GROUP BY, ringkasan itu mencakup setiap baris di hasil query.

COUNT: Baris vs Nilai

COUNT punya tiga bentuk, dan perbedaannya penting:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Ada',   88.00),
    ('Carl',  NULL),
    ('Ada',   NULL);

SELECT
    COUNT(*)               AS rows_total,
    COUNT(amount)          AS rows_with_amount,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

• COUNT(*) menghitung baris. NULL ikut dihitung. Selalu mengembalikan angka.
• COUNT(column) menghitung nilai yang bukan NULL di kolom itu.
• COUNT(DISTINCT column) menghitung nilai unik yang bukan NULL.

Lima baris, tiga di antaranya punya amount, tiga customer yang berbeda. Kalau kamu pernah melihat COUNT(amount) dan bingung kenapa hasilnya lebih kecil dari COUNT(*), itulah alasannya: NULL tidak dihitung.

SUM, AVG, MIN, MAX

Fungsi agregat aritmetika bekerja seperti yang kamu harapkan, dengan satu aturan yang tidak terlihat: semuanya melewati NULL:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT
    SUM(amount) AS total,
    AVG(amount) AS mean,
    MIN(amount) AS smallest,
    MAX(amount) AS largest,
    COUNT(amount) AS counted
FROM orders;

AVG adalah (10 + 20 + 30) / 3 = 20.0, bukan 60 / 4 = 15.0. Pembaginya adalah jumlah nilai yang bukan NULL. Kalau bukan itu yang kamu mau, misalnya kamu lebih suka menganggap data yang hilang sebagai nol, tulis secara eksplisit:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT AVG(COALESCE(amount, 0)) FROM orders;

MIN dan MAX juga bekerja untuk teks dan tanggal. Teks dibandingkan secara leksikografis, dan tanggal dibandingkan sebagai string ISO untuk format tanggal standar.

SUM vs TOTAL

SQLite punya fungsi agregat kedua yang mirip penjumlahan, yaitu TOTAL, yang memperbaiki dua hal menyebalkan dari SUM:

CREATE TABLE empty_table (amount REAL);

SELECT SUM(amount), TOTAL(amount) FROM empty_table;

• SUM dari nol baris mengembalikan NULL. TOTAL mengembalikan 0.0.
• SUM dari nilai yang semuanya NULL mengembalikan NULL. TOTAL mengembalikan 0.0.
• TOTAL selalu mengembalikan bilangan floating-point, jadi tidak pernah mengalami overflow aritmetika bilangan bulat.

Kekurangannya: TOTAL tidak standar, dan hasil yang selalu REAL bisa mengejutkanmu kalau kamu mengharapkan bilangan bulat. Pakai fungsi ini kalau "tidak ada baris berarti nol" adalah jawaban yang tepat untuk aplikasimu, dan tetap pakai SUM kalau kamu ingin perilaku sesuai standar SQL.

DISTINCT di Dalam Fungsi Agregat

DISTINCT bisa dipakai di dalam fungsi agregat apa pun, tidak hanya COUNT. Ia membuang nilai duplikat sebelum agregasi berjalan:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Carl',  10.00);

SELECT
    SUM(amount)          AS sum_all,
    SUM(DISTINCT amount) AS sum_unique,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

SUM(amount) menjumlahkan amount dari setiap baris. SUM(DISTINCT amount) menjumlahkan setiap amount yang unik satu kali saja. Ini berguna untuk hal seperti "total dari nominal invoice yang unik", tapi jarang itu yang kamu butuhkan. COUNT(DISTINCT customer) adalah yang paling sering dipakai.

FILTER: Mengagregasi Sebagian Baris

Kalau kamu ingin mengagregasi hanya sebagian baris, langkah yang terpikir pertama adalah WHERE. Tapi WHERE menyaring semuanya, jadi dengan cara itu kamu tidak bisa menggabungkan "hitung pesanan yang dibayar" dan "hitung refund" dalam query yang sama. FILTER menyelesaikan masalah ini:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'),
    (15.50, 'paid'),
    (88.00, 'paid'),
    (10.00, 'refunded'),
    (25.00, 'pending');

SELECT
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_count,
    COUNT(*) FILTER (WHERE status = 'refunded')  AS refund_count,
    SUM(amount) FILTER (WHERE status = 'paid')   AS paid_revenue
FROM orders;

Setiap klausa FILTER (WHERE ...) hanya berlaku untuk satu fungsi agregat itu. Satu kali menelusuri tabel, beberapa irisan diringkas sekaligus. Sebelum FILTER ada, orang menulis SUM(CASE WHEN status = 'paid' THEN amount END): idenya sama, hanya lebih banyak mengetik.

GROUP_CONCAT: Menggabungkan String

GROUP_CONCAT berbeda dari yang lain. Alih-alih mengembalikan angka, fungsi ini menyambung nilai-nilainya menjadi satu string:

CREATE TABLE tags (post_id INTEGER, tag TEXT);
INSERT INTO tags VALUES
    (1, 'sqlite'),
    (1, 'database'),
    (1, 'tutorial'),
    (2, 'python'),
    (2, 'sqlite');

SELECT
    post_id,
    GROUP_CONCAT(tag)        AS default_separator,
    GROUP_CONCAT(tag, ' | ') AS pipe_separated
FROM tags
GROUP BY post_id;

Pemisah bawaannya adalah koma. Berikan argumen kedua untuk memakai pemisah lain. Urutannya tidak dijamin kecuali kamu menulis pemanggilannya sebagai GROUP_CONCAT(tag ORDER BY tag). Ini berguna kalau output-nya tampil di UI dan kamu ingin urutannya stabil.

Agregasi Tanpa GROUP BY

Setiap contoh sejauh ini yang memakai fungsi agregat tanpa GROUP BY menghasilkan tepat satu baris. Itulah aturannya: SELECT dengan fungsi agregat dan tanpa GROUP BY adalah ringkasan satu baris dari seluruh tabel (setelah WHERE).

Kamu bisa mencampur fungsi agregat dengan bebas:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'), (15.50, 'paid'), (88.00, 'paid'),
    (10.00, 'refunded'), (25.00, 'pending');

SELECT
    COUNT(*)    AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders
WHERE status = 'paid';

Yang tidak bisa kamu lakukan adalah mencampur kolom yang tidak diagregasi dengan fungsi agregat dan berharap hasilnya masuk akal:

-- Allowed by SQLite, but the value of `customer` is arbitrary.
SELECT customer, SUM(amount) FROM orders;

SQLite tidak akan memberi error di sini (database lain akan), tapi ia akan memilih nama customer secara acak untuk ditampilkan di samping total. Kalau kamu ingin jumlah per customer, kamu butuh GROUP BY, yang dibahas di halaman berikutnya.

Berikutnya: GROUP BY dan HAVING

Fungsi agregat atas seluruh tabel menjawab "berapa totalnya." Fungsi agregat per kelompok, misalnya per customer, per bulan, atau per status, menjawab pertanyaan yang lebih menarik. GROUP BY adalah cara membagi baris ke dalam kelompok sebelum diagregasi, dan HAVING adalah cara menyaring hasil agregasinya. Itu yang berikutnya.