SQL 02. Total Pendapatan per Kategori (GROUP BY & SUM) SQL 02. Total Revenue by Category (GROUP BY & SUM)

Hitung total omset dan jumlah produk per kategori dengan filter threshold pendapatan. Calculate total revenue and product count per category filtered by threshold.

Contoh Logika Serupa: Agregasi Nilai Transaksi per Cabang Similar Pattern Example: Aggregating Branch Sales

Referensi Kode

Mengelompokkan data per wilayah, menjumlahkan nilai total, menghitung jumlah invoice, dan memfilter total dengan HAVING: Grouping records by region with sum calculation and HAVING filter:

-- Contoh GROUP BY dengan SUM, COUNT, dan filter HAVING
SELECT branch_city, 
       SUM(total_amount) AS branch_revenue,
       COUNT(*) AS total_invoices
FROM sales
GROUP BY branch_city
HAVING SUM(total_amount) > 1000000
ORDER BY branch_revenue DESC;

Langkah & Spesifikasi Tugas Step-by-Step Requirements

Contoh Kasus & Ketentuan Example Cases & Rules

Database Schema Setup

In-Memory SQLite

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100), category VARCHAR(50), price INT, stock INT);
INSERT INTO products VALUES (1, 'Laptop Pro', 'Electronics', 1500000, 2), (2, 'Mouse Wireless', 'Electronics', 150000, 5), (3, 'Kaos Polos', 'Apparel', 75000, 4), (4, 'Jaket Hoodie', 'Apparel', 250000, 3), (5, 'Stiker Dev', 'Merchandise', 15000, 10);

Target Output (Expected) Expected Output

[
    {
        "category": "Electronics",
        "total_revenue": 3750000,
        "total_products": 2
    },
    {
        "category": "Apparel",
        "total_revenue": 1050000,
        "total_products": 2
    }
]

Petunjuk Pengerjaan:

Gunakan `SUM(price * stock) AS total_revenue` untuk mengalikan harga dan stok sebelum dijumlahkan.
Gunakan `HAVING` bukan `WHERE` untuk memfilter hasil fungsi agregat SUM.
Urutkan dengan `ORDER BY total_revenue DESC`.

SELECT category, SUM(price * stock) AS total_revenue, COUNT(*) AS total_products FROM products GROUP BY category HAVING SUM(price * stock) > 500000 ORDER BY total_revenue DESC;

query.sql

✨ Kode dirapikan!

Login Diperlukan

Masuk dengan akun Google untuk mengeksekusi kodingan & query sandbox di server secara interaktif.

Masuk dengan Google Quick Learner Demo Login