Hari 22: Subquery di FROM & SELECT

Subquery di FROM (Derived Table)

Subquery di FROM menghasilkan "tabel sementara" yang bisa di-query lagi.

-- Rata-rata penjualan per pelanggan
SELECT
  AVG(total_per_pelanggan) AS rata_belanja_pelanggan
FROM (
  SELECT pelanggan_id, SUM(total_harga) AS total_per_pelanggan
  FROM pesanan
  GROUP BY pelanggan_id
) AS ringkasan;

-- Ranking produk per kategori berdasarkan stok
SELECT *
FROM (
  SELECT
    nama_produk, kategori_id, stok,
    ROW_NUMBER() OVER (PARTITION BY kategori_id ORDER BY stok DESC) AS rank_stok
  FROM produk
) WHERE rank_stok = 1;

Subquery di SELECT (Correlated)

-- Hitung jumlah pesanan per pelanggan langsung di SELECT
SELECT
  pl.nama,
  (SELECT COUNT(*) FROM pesanan p WHERE p.pelanggan_id = pl.id) AS jumlah_pesanan
FROM pelanggan pl
ORDER BY jumlah_pesanan DESC;