Cara Menghitung Persentase di SQL (5 Metode Berbeda)

Kenapa Perlu Menghitung Persentase di SQL?

"Berapa persen customer kita dari Jakarta?"
"Market share produk A berapa persen dari total revenue?"
"Persentase order yang completed gimana trennya bulan ke bulan?"

Pertanyaan kayak gini pasti sering banget muncul di kerjaan Data Analyst. Dan jawabannya: persentase.

Menghitung persentase di SQL itu skill dasar yang wajib kamu kuasai. Kabar baiknya, ada beberapa cara berbeda tergantung kebutuhan kamu. Kita bahas satu per satu.

Apa yang Akan Kamu Pelajari

• Metode 1: Persentase sederhana dengan COUNT
• Metode 2: Persentase dengan SUM
• Metode 3: Persentase per grup (GROUP BY)
• Metode 4: Running percentage dengan Window Function
• Metode 5: Persentase kumulatif
• Handling division by zero
• Formatting hasil persentase

Dataset yang Akan Kita Pakai

Buat tutorial ini, kita pakai dataset market share e-commerce Indonesia. Data penjualan dari berbagai platform marketplace.

Tabel: penjualan

Tabel: customers

Metode 1: Persentase Sederhana dengan COUNT

Ini cara paling basic. Kamu mau tau berapa persen customer dari Jakarta.

SELECT
    ROUND(
        100.0 * COUNT(CASE WHEN kota = 'Jakarta' THEN 1 END) / COUNT(*),
        2
    ) AS persen_jakarta
FROM customers;

Hasil:

50% customer kita dari Jakarta. Simple kan?

Breakdown query-nya:
- COUNT(CASE WHEN kota = 'Jakarta' THEN 1 END) = hitung yang Jakarta aja
- COUNT(*) = total semua customer
- 100.0 * = kalikan 100 biar jadi persentase (pake 100.0 biar hasilnya decimal)
- ROUND(..., 2) = bulatkan 2 angka di belakang koma

Metode 2: Persentase dengan SUM

Kalau mau hitung persentase dari nilai (bukan count), pake SUM.

Misalnya, berapa persen revenue dari platform Tokopedia?

SELECT
    ROUND(
        100.0 * SUM(CASE WHEN platform = 'Tokopedia' THEN revenue ELSE 0 END) / SUM(revenue),
        2
    ) AS persen_revenue_tokped
FROM penjualan;

Hasil:

Tokopedia menyumbang 38.33% dari total revenue. Lumayan gede ya.

Versi alternatif pake subquery:

SELECT
    ROUND(
        100.0 * (SELECT SUM(revenue) FROM penjualan WHERE platform = 'Tokopedia')
        / SUM(revenue),
        2
    ) AS persen_revenue_tokped
FROM penjualan;

Hasilnya sama, tinggal pilih mana yang lebih readable buat kamu.

Metode 3: Persentase per Grup dengan GROUP BY

Nah ini yang sering dipake di reporting. Kamu mau liat persentase revenue tiap platform.

SELECT
    platform,
    SUM(revenue) AS total_revenue,
    ROUND(
        100.0 * SUM(revenue) / (SELECT SUM(revenue) FROM penjualan),
        2
    ) AS persen_revenue
FROM penjualan
GROUP BY platform
ORDER BY persen_revenue DESC;

Hasil:

Sekarang kamu bisa liat market share tiap platform. Tokopedia dan Shopee dominan dengan combined 73% market share.

Alternatif Pake Window Function

Cara yang lebih elegan pake window function:

SELECT
    platform,
    SUM(revenue) AS total_revenue,
    ROUND(
        100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (),
        2
    ) AS persen_revenue
FROM penjualan
GROUP BY platform
ORDER BY persen_revenue DESC;

SUM(SUM(revenue)) OVER () itu grand total dari semua platform. Lebih clean karena ga perlu subquery.

Metode 4: Running Percentage dengan Window Function

Running percentage itu persentase yang "berjalan" dari row pertama sampai row saat ini. Berguna buat liat kontribusi kumulatif.

Misalnya, kalau platform diurutkan dari revenue terbesar, berapa persen akumulasi di setiap level?

WITH platform_revenue AS (
    SELECT
        platform,
        SUM(revenue) AS total_revenue
    FROM penjualan
    GROUP BY platform
)
SELECT
    platform,
    total_revenue,
    SUM(total_revenue) OVER (ORDER BY total_revenue DESC) AS running_total,
    ROUND(
        100.0 * SUM(total_revenue) OVER (ORDER BY total_revenue DESC)
        / SUM(total_revenue) OVER (),
        2
    ) AS running_persen
FROM platform_revenue
ORDER BY total_revenue DESC;

Hasil:

Baris terakhir pas di 100%. Semua platform udah keakumulasi. Kadang kamu bisa liat selisih kecil kayak 99.99% atau 100.01% gara-gara pembulatan, dan itu normal.

Insight-nya: Tokopedia + Shopee aja udah 73% market share. Classic 80/20 rule nih.

Metode 5: Persentase Kumulatif (Cumulative Percentage)

Mirip running percentage, tapi biasanya dipake buat analisis distribusi. Misalnya, distribusi customer berdasarkan total order.

WITH customer_orders AS (
    SELECT
        customer_id,
        nama,
        total_order,
        ROW_NUMBER() OVER (ORDER BY total_order DESC) AS rank
    FROM customers
),
total_customers AS (
    SELECT COUNT(*) AS total FROM customers
)
SELECT
    co.rank,
    co.nama,
    co.total_order,
    ROUND(100.0 * co.rank / tc.total, 2) AS persen_customer,
    ROUND(
        100.0 * SUM(co.total_order) OVER (ORDER BY co.total_order DESC)
        / SUM(co.total_order) OVER (),
        2
    ) AS persen_order_kumulatif
FROM customer_orders co
CROSS JOIN total_customers tc
ORDER BY co.rank;

Hasil:

Dari sini keliatan: 30% customer teratas (Rudi, Budi, Dewi) menyumbang hampir 57% dari total order. Power users!

Persentase per Kategori dalam Grup

Case yang lebih kompleks: persentase revenue tiap kategori DALAM setiap platform.

SELECT
    platform,
    kategori,
    SUM(revenue) AS revenue,
    ROUND(
        100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (PARTITION BY platform),
        2
    ) AS persen_dalam_platform
FROM penjualan
GROUP BY platform, kategori
ORDER BY platform, persen_dalam_platform DESC;

Hasil:

Di Tokopedia, Elektronik dominan banget (76%). Sementara Shopee lebih balance antara Elektronik dan Fashion.

Handling Division by Zero

Ini penting banget. Kalau total-nya 0, query bakal error. Gimana cara handle-nya?

Cara 1: NULLIF

SELECT
    platform,
    SUM(revenue) AS total_revenue,
    ROUND(
        100.0 * SUM(revenue) / NULLIF(SUM(SUM(revenue)) OVER (), 0),
        2
    ) AS persen_revenue
FROM penjualan
GROUP BY platform;

NULLIF(x, 0) bakal return NULL kalau x = 0. Jadi hasilnya NULL, bukan error.

Cara 2: CASE WHEN

SELECT
    platform,
    SUM(revenue) AS total_revenue,
    CASE
        WHEN SUM(SUM(revenue)) OVER () = 0 THEN 0
        ELSE ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 2)
    END AS persen_revenue
FROM penjualan
GROUP BY platform;

Lebih verbose, tapi kamu bisa set default value (0 atau NULL atau apa aja).

Cara 3: COALESCE + NULLIF

SELECT
    platform,
    SUM(revenue) AS total_revenue,
    COALESCE(
        ROUND(100.0 * SUM(revenue) / NULLIF(SUM(SUM(revenue)) OVER (), 0), 2),
        0
    ) AS persen_revenue
FROM penjualan
GROUP BY platform;

Kalau hasil pembagian NULL, ganti dengan 0.

Formatting Hasil Persentase

Tambahkan Symbol %

SELECT
    platform,
    SUM(revenue) AS total_revenue,
    ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 2) || '%' AS persen_revenue
FROM penjualan
GROUP BY platform;

Hasil:

Format dengan TO_CHAR (PostgreSQL)

SELECT
    platform,
    TO_CHAR(SUM(revenue), 'FM999,999,999,999') AS total_revenue,
    TO_CHAR(
        100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (),
        'FM990.00%'
    ) AS persen_revenue
FROM penjualan
GROUP BY platform;

Hasil:

Lebih readable untuk reporting.

Common Mistakes yang Harus Dihindari

Mistake 1: Lupa Kalikan 100

-- SALAH (hasilnya 0.3833, bukan 38.33%)
SELECT SUM(revenue) / (SELECT SUM(revenue) FROM penjualan) AS persen
FROM penjualan
WHERE platform = 'Tokopedia';

-- BENAR
SELECT 100.0 * SUM(revenue) / (SELECT SUM(revenue) FROM penjualan) AS persen
FROM penjualan
WHERE platform = 'Tokopedia';

Mistake 2: Integer Division

-- SALAH (integer division, hasilnya 0 atau 1)
SELECT 100 * COUNT(CASE WHEN kota = 'Jakarta' THEN 1 END) / COUNT(*) AS persen
FROM customers;

-- BENAR (pake 100.0 biar decimal)
SELECT 100.0 * COUNT(CASE WHEN kota = 'Jakarta' THEN 1 END) / COUNT(*) AS persen
FROM customers;

Mistake 3: Ga Handle Division by Zero

-- POTENSI ERROR kalau total = 0
SELECT 100.0 * SUM(revenue) / SUM(total_revenue) AS persen
FROM empty_table;

-- AMAN
SELECT 100.0 * SUM(revenue) / NULLIF(SUM(total_revenue), 0) AS persen
FROM empty_table;

Mistake 4: Subquery Berulang

-- KURANG EFISIEN (subquery dijalankan berkali-kali)
SELECT
    platform,
    100.0 * SUM(revenue) / (SELECT SUM(revenue) FROM penjualan) AS persen
FROM penjualan
GROUP BY platform;

-- LEBIH EFISIEN (pake window function)
SELECT
    platform,
    100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER () AS persen
FROM penjualan
GROUP BY platform;

Tips dan Best Practices

1. Pilih Presisi yang Tepat

• Untuk dashboard: 1-2 decimal places
• Untuk analisis detail: 2-4 decimal places
• Untuk financial: sesuai requirement bisnis

2. Validasi Total = 100%

Kalau kamu hitung persentase per grup, totalnya harus 100% (atau mendekati karena rounding).

SELECT SUM(persen_revenue) AS total_persen
FROM (
    SELECT ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 2) AS persen_revenue
    FROM penjualan
    GROUP BY platform
) sub;

3. Gunakan CTE untuk Query Kompleks

WITH total_revenue AS (
    SELECT SUM(revenue) AS total FROM penjualan
),
platform_revenue AS (
    SELECT platform, SUM(revenue) AS revenue
    FROM penjualan
    GROUP BY platform
)
SELECT
    pr.platform,
    pr.revenue,
    ROUND(100.0 * pr.revenue / tr.total, 2) AS persen
FROM platform_revenue pr
CROSS JOIN total_revenue tr;

Lebih readable dan gampang di-debug.

4. Comment Kalkulasi yang Kompleks

SELECT
    platform,
    SUM(revenue) AS total_revenue,
    -- Market share = (platform revenue / total revenue) * 100
    ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 2) AS market_share_pct
FROM penjualan
GROUP BY platform;

Latihan

Coba kerjain query ini:

Soal: Hitung persentase customer aktif vs inactive per kota. Output-nya: kota, total_customer, persen_active, persen_inactive.

SELECT
    kota,
    COUNT(*) AS total_customer,
    ROUND(
        100.0 * COUNT(CASE WHEN status = 'active' THEN 1 END) / COUNT(*),
        2
    ) AS persen_active,
    ROUND(
        100.0 * COUNT(CASE WHEN status = 'inactive' THEN 1 END) / COUNT(*),
        2
    ) AS persen_inactive
FROM customers
GROUP BY kota
ORDER BY total_customer DESC;

FAQ

Kenapa hasil persentase kamu selalu 0 atau 1?

Itu gara-gara integer division. Kalau kamu nulis 100 * a / b dan dua-duanya integer, database bakal buang angka di belakang koma. Solusinya gampang kok: pakai 100.0 (ada .0-nya) biar hasilnya decimal. Ini kesalahan yang paling sering muncul waktu orang pertama kali hitung persentase di SQL.

Gimana cara aman handle division by zero?

Pakai NULLIF di penyebutnya: 100.0 * nilai / NULLIF(total, 0). Kalau total-nya 0, hasilnya NULL, bukan error yang bikin query gagal. Mau hasilnya 0 dan bukan NULL? Bungkus lagi pakai COALESCE. Dua fungsi ini udah cukup buat hampir semua kasus pembagian di reporting.

Mending pake subquery atau window function buat persentase per grup?

Window function. SUM(SUM(revenue)) OVER () ngitung grand total sekali jalan, jadi query-nya lebih rapi dan biasanya lebih cepat dari subquery yang dieksekusi berulang. Tapi kalau kamu masih baru, subquery lebih gampang dibaca. Mulai dari situ dulu, pindah ke window function pas udah nyaman.

Kenapa total persentase per grup nggak pas 100%?

Karena rounding. ROUND(..., 2) motong tiap angka jadi 2 desimal, jadi jumlahnya bisa 99.99% atau 100.01%. Selisih sekecil itu normal dan nggak perlu dibenerin. Kalau report kamu wajib pas 100%, bulatkan baris terakhir sebagai sisa: 100 dikurangi jumlah baris lainnya.

Kesimpulan

Menghitung persentase di SQL itu gampang kalau kamu tau caranya. Inget poin-poin ini:

1. Kalikan 100.0 (bukan 100) biar hasilnya decimal
2. NULLIF atau CASE WHEN buat handle division by zero
3. Pake window function buat persentase per grup yang lebih efisien
4. Running percentage berguna buat analisis kumulatif
5. Format hasilnya biar readable untuk reporting

Persentase itu bahasa universal di bisnis. Stakeholder lebih gampang ngerti "market share 40%" daripada "revenue 2.16 milyar". Jadi kuasai skill ini ya!

Happy calculating!

Selanjutnya

Kalau kamu udah paham cara hitung persentase, next step-nya:
- Window Functions - buat analisis yang lebih advanced
- GROUP BY dan HAVING - dasar agregasi data
- Cohort Analysis - analisis retention dengan persentase