Jebakan Umum: NULL dalam Agregat

Agregasi Mengabaikan NULL

Jebakan Klasik: COUNT(*) vs COUNT(col)

SELECT COUNT(*)         FROM users;      -- 1000
SELECT COUNT(email)     FROM users;      -- 950  (50 users have NULL email)

Bug: AVG Tidak Menganggap NULL sebagai Nol

SELECT AVG(rating) FROM reviews;        -- 5
SELECT AVG(COALESCE(rating, 0)) FROM reviews;   -- 3.33

Tabel Kosong = Agregasi NULL

SELECT COALESCE(SUM(total), 0) AS revenue
FROM orders
WHERE user_id = 999999;       -- safe even when user has no orders

SUM pada Kolom NULL

GROUP BY dengan Kunci Pengelompokan NULL

SELECT country, COUNT(*) FROM users
GROUP BY country;
-- → ('US', 100), ('CA', 30), (NULL, 5)

Menghitung Nilai Berbeda dengan NULL

INSERT INTO t (c) VALUES (1),(2),(NULL),(NULL),(2);
SELECT COUNT(DISTINCT c) FROM t;   -- 2 (NULL not counted)

DISTINCT pada Banyak Kolom

SELECT COUNT(*) FROM (
  SELECT DISTINCT a, b FROM t
) sub;

Waspadai Penggabungan Implisit dengan Tabel Opsional

SELECT u.id, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;          -- COUNT(o.id) gives 0 for users with no orders

FILTER untuk Penghitungan Bersyarat

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

Selalu Uji Agregasi dengan NULL

Ringkasan

Pemeriksaan Singkat