Joins dengan grouping
Kekuatan sebenarnya datang dari menggabungkan apa yang telah Anda pelajari: join dua tabel, group baris yang digabungkan, dan summarise (ringkaskan) setiap kelompok dengan fungsi agregat.
Laporan umum adalah "berapa banyak yang dihabiskan setiap pelanggan?" — gabungkan pelanggan dengan pesanan mereka, kelompokkan berdasarkan pelanggan, dan SUM totalnya.
SELECT customer.name,
COUNT(*) AS orders,
ROUND(SUM(orders.total), 2) AS spent
FROM customer
INNER JOIN orders ON customer.id = orders.customer_id
GROUP BY customer.id, customer.name
ORDER BY customer.name;
Membacanya secara berurutan: join tabel-tabel tersebut, group baris-baris berdasarkan pelanggan, lalu untuk setiap kelompok hitung jumlah pesanan dan tambahkan totalnya.
Sebuah INNER JOIN akan menghapus Ben dan Dan karena mereka tidak memiliki pesanan. Sebuah LEFT JOIN akan menyimpan setiap pelanggan dan menyediakan NULL untuk pesanan yang tidak cocok. COUNT(*) akan menghitung baris placeholder itu sebagai satu; COUNT(orders.id) melewatkan id NULL-nya dan dengan benar memberikan nol. SUM mengembalikan NULL ketika tidak ada total non-NULL, jadi gunakan COALESCE(..., 0) untuk laporan belanja nol.
Untuk menghitung hanya pesanan minimal 25 sambil tetap menyimpan semua pelanggan, letakkan kondisi pesanan di ON:
SELECT customer.name, COUNT(orders.id) AS qualifying_orders,
COALESCE(ROUND(SUM(orders.total), 2), 0) AS spent
FROM customer
LEFT JOIN orders ON customer.id = orders.customer_id AND orders.total >= 25
GROUP BY customer.id, customer.name
ORDER BY customer.name;
Hasilnya adalah Ada: 1 dan 25; Ben: 0 dan 0; Cara: 1 dan 40; Dan: 0 dan 0. Memindahkan orders.total >= 25 ke WHERE akan menghapus Ben dan Dan: total NULL mereka tidak memenuhi perbandingan tersebut. Kelompokkan berdasarkan id serta nama agar dua pelanggan dengan nama yang sama tetap terpisah.
- Join terlebih dahulu, kemudian
GROUP BYuntuk meringkas baris yang digabungkan. - Qualify (tandai) sebuah kolom dengan nama tabelnya ketika kedua tabel membaginya.
Menggabungkan tabel
An inner join membuang baris yang memiliki tidak ada kecocokan.
Kesulitan 4/5 · 280 koin + bonus kecil pada penyelesaian pertama hari ini
Untuk setiap customer yang memiliki orders, tampilkan name-nya, jumlah orders-nya sebagai orders, dan total pengeluaran (2 d.p.) sebagai spent. Join, group by customer.id, dan order by name.
Klik Jalankan untuk melihat output di sini.
Kesulitan 4/5 · 280 koin + bonus kecil pada penyelesaian pertama hari ini
Tampilkan INNER JOIN untuk Ben dan Dan, yang tidak memiliki pesanan. Gunakan LEFT JOIN untuk menjaga semua pelanggan, dan COUNT(orders.id) agar pelanggan tanpa pesanan menampilkan 0. Urutkan berdasarkan name.
Klik Jalankan untuk melihat output di sini.
Kesulitan 4/5 · 280 koin + bonus kecil pada penyelesaian pertama hari ini
Simpan setiap pelanggan, termasuk yang tidak memiliki pesanan yang memenuhi syarat. Tampilkan name, jumlah pesanan dengan total > 30 sebagai orders, dan total mereka sebagai spent (dibulatkan ke 2 angka desimal, atau 0 jika tidak ada). Urutkan berdasarkan nama. Tempatkan filter agar pelanggan yang tidak cocok tetap muncul.
Klik Jalankan untuk melihat output di sini.