Join, Group dan Pivot

Join dan group hampir selalu digunakan pada basis data relational yang memenuhi normalisasi. Tabel pivot atau cross-tab sering diperlukan ketika ingin melihat informasi dari dimensi yang berbeda. Pada contoh yang lalu, setelah dilakukan normalisasi, menghasilkan dua buah tabel, yaitu tabel turai (yang berisikan uraian) dan transaksi (yang berisikan transaksi setiap uraian). Lihat kembali posting Dzaky tentang intro rollup, null-rollup, dan ifnull-rollup yang membahas tentang group with rollup.
Setelah melalui beberapa studi kasus, Dzaky mendapatkan tabel seperti ini

Tabel Uraian

pk	uraian	kode
1	Makan siang	K
2	Makan malam	K
3	Beli rokok	K
4	Makan Sate	K
5	Sarapan Pagi	K
6	Honor	M

Tabel Transaksi

tanggal	pk	jumlah
2012-02-01	1	15000
2012-02-01	2	12500
2012-02-01	3	10000
2012-02-01	4	25000
2012-02-02	5	5000
2012-02-02	1	12500
2012-02-02	2	20000
2012-02-02	3	10000
2012-01-31	6	750000
2012-02-05	6	500000
2012-04-03	3	25000
2012-04-03	4	40000
2012-04-03	3	25000
2012-04-03	4	40000
2012-04-03	3	25000
2012-04-03	4	40000
2012-04-03	3	25000
2012-04-03	4	40000
2012-04-19	2	2460

Untuk mendapatkan kembali informasi lengkap dari kedua tabel, dilakukan dengan klausa JOIN.

SELECT t.tanggal, u.uraian, u.kode, t.jumlah
FROM transaksi t JOIN ( turai u )
WHERE t.pk = u.pk;
dan hasilnya bisa dilihat disini

tanggal	uraian	kode	Jumlah
2012-02-01	Makan siang	K	15000
2012-02-02	Makan siang	K	12500
2012-02-01	Makan malam	K	12500
2012-02-02	Makan malam	K	20000
2012-04-19	Makan malam	K	2460
2012-02-01	Beli rokok	K	10000
2012-02-02	Beli rokok	K	10000
2012-04-03	Beli rokok	K	25000
2012-04-03	Beli rokok	K	25000
2012-04-03	Beli rokok	K	25000
2012-04-03	Beli rokok	K	25000
2012-02-01	Makan Sate	K	25000
2012-04-03	Makan Sate	K	40000
2012-04-03	Makan Sate	K	40000
2012-04-03	Makan Sate	K	40000
2012-04-03	Makan Sate	K	40000
2012-02-02	Sarapan Pagi	K	5000
2012-01-31	Honor	M	750000
2012-02-05	Honor	M	500000

Akan semakin menarik bila informasi yang disajikan lebih simple, misal sudah berapa banyak uang yang digunakan untuk setiap uraian. Untuk itu diperlukan klausa group dan fungsi agregat sum.

SELECT t.tanggal, u.uraian, u.kode,
sum(t.jumlah) as jumlah
FROM transaksi t JOIN ( turai u )
WHERE t.pk = u.pk
GROUP BY u.uraian

dan hasilnya bisa dilihat disini

tanggal	uraian	kode	Jumlah
2012-02-01	Beli rokok	K	120000
2012-01-31	Honor	M	1250000
2012-02-01	Makan malam	K	34960
2012-02-01	Makan Sate	K	185000
2012-02-01	Makan siang	K	27500
2012-02-02	Sarapan Pagi	K	5000

Sudah kelihatan lebih informatif. Apa lagi? Masih ada, dari kode yang ada menyatakan M[asuk] dan K[keluar]. Bagaimana kalau untuk uang keluar ditandai dengan negatif?

SELECT t.tanggal, u.uraian,
SUM(IF(kode='K',-t.jumlah,t.jumlah)) AS JUMLAH
FROM transaksi t JOIN ( turai u )
WHERE t.pk = u.pk
GROUP BY uraian
dan hasilnya bisa dilihat disini

tanggal	uraian	Jumlah
2012-02-01	Beli rokok	-120000
2012-01-31	Honor	1250000
2012-02-01	Makan malam	-34960
2012-02-01	Makan Sate	-185000
2012-02-01	Makan siang	-27500
2012-02-02	Sarapan Pagi	-5000

Ah..ha sudah semakin menarik informasinya. Oh iya, berapa sisa uang ayah? Jangan-jangan....

SELECT t.tanggal , IFNULL(u.uraian,'TOTAL') AS URAIAN,
SUM(IF(kode='K',-t.jumlah,t.jumlah)) AS JUMLAH
FROM transaksi t JOIN ( turai u )
WHERE t.pk = u.pk
GROUP BY uraian with ROLLUP

dan hasilnya bisa dilihat disini

tanggal	uraian	Jumlah
2012-02-01	Beli rokok	-120000
2012-01-31	Honor	1250000
2012-02-01	Makan malam	-34960
2012-04-03	Makan Sate	-185000
2012-02-01	Makan siang	-27500
2012-02-02	Sarapan Pagi	-5000
2012-02-02	TOTAL	877540

Ho..ho.. semakin asyik saja. Sekarang Dzaky mau kelompokkan berdasarkan uang yang masuk dan uang yang keluar serta selisihnya.

SELECT IF(u.kode='K','Uang Keluar',
IF(u.kode='M','Uang Masuk','SELISIH')) AS KETERANGAN,
SUM(IF(kode='K',-t.jumlah,t.jumlah)) AS JUMLAH
FROM transaksi t JOIN ( turai u )
WHERE t.pk = u.pk
GROUP BY u.kode with rollup
dan hasilnya bisa dilihat disini

Keterangan	Jumlah
Uang Keluar	-372460
Uang Masuk	1250000
SELISIH	877540

Oke..oke.. bagaimana dengan pivot atau cross-tab?