Belajar SQL | Membuat laporan summary data dari table di database bisa dibilang gampang-gampang susah. Gampang ketika hanya membuat summary dengan sumber data satu buah table saja. Namun jadi makin rumit ketika summary melibatkan 2 (dua) table atau lebih. Silakan pelajari TUTORIAL berikut ini sebagai salah satu contoh kasus untuk membuat summary data dari 3 buah table terpisah.
Pertama-tama kita buat Table MASTER untuk sample data menggunakan perintah SQL CREATE TABLE … AS SELECT … kemudian tampilkan isinya untuk memastikan.
mysql> create table t_master as
-> select 1 id, 'A' data union all
-> select 2 id, 'B' data union all
-> select 3 id, 'C' data ;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_master ;
+----+------+
| id | data |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)
Kemudian buat table TRANSAKSI pertama juga menggunakan perintah SQL CREATE TABLE … AS SELECT … kemudian tampilkan isinya untuk memastikan.
mysql> create table t_transaksi_1 as
-> select 1 id, 'DOC 1' doc, 2000 value union all
-> select 1 id, 'DOC 1' doc, 1000 value union all
-> select 2 id, 'DOC 2' doc, 5000 value union all
-> select 3 id, 'DOC 3' doc, 3000 value union all
-> select 3 id, 'DOC 4' doc, 3000 value union all
-> select 3 id, 'DOC 4' doc, 2000 value ;
Query OK, 6 rows affected (0.30 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t_transaksi_1 ;
+----+-------+-------+
| id | doc | value |
+----+-------+-------+
| 1 | DOC 1 | 2000 |
| 1 | DOC 1 | 1000 |
| 2 | DOC 2 | 5000 |
| 3 | DOC 3 | 3000 |
| 3 | DOC 4 | 3000 |
| 3 | DOC 4 | 2000 |
+----+-------+-------+
6 rows in set (0.00 sec)
Berikutnya buat table TRANSAKSI kedua juga menggunakan perintah SQL CREATE TABLE … AS SELECT … kemudian tampilkan isinya untuk memastikan.
mysql> create table t_transaksi_2 as
-> select 2 id, 'DOC 2' doc, 2000 value union all
-> select 2 id, 'DOC 2' doc, 2000 value union all
-> select 3 id, 'DOC 3' doc, 4000 value union all
-> select 3 id, 'DOC 4' doc, 5000 value union all
-> select 3 id, 'DOC 4' doc, 1000 value ;
Query OK, 5 rows affected (0.15 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t_transaksi_2 ;
+----+-------+-------+
| id | doc | value |
+----+-------+-------+
| 2 | DOC 2 | 2000 |
| 2 | DOC 2 | 2000 |
| 3 | DOC 3 | 4000 |
| 3 | DOC 4 | 5000 |
| 3 | DOC 4 | 1000 |
+----+-------+-------+
5 rows in set (0.00 sec)
Sekarang kita buatkan summary untuk table TRANSAKSI pertama, menggunakan GROUP BY dan fungsi aggregat SUM()
mysql> select tm.id, data, doc, sum(value) value1
-> from t_master tm
-> join t_transaksi_1 tr on tm.id=tr.id
-> group by id, doc
-> ;
+----+------+-------+---------+
| id | data | doc | value11 |
+----+------+-------+---------+
| 1 | A | DOC 1 | 3000 |
| 2 | B | DOC 2 | 5000 |
| 3 | C | DOC 3 | 3000 |
| 3 | C | DOC 4 | 5000 |
+----+------+-------+---------+
4 rows in set (0.00 sec)
Sekarang kita buatkan summary untuk table TRANSAKSI kedua, menggunakan GROUP BY dan fungsi aggregat SUM().
mysql> select tm.id, data, doc, sum(value) value2
-> from t_master tm
-> join t_transaksi_2 tr on tm.id=tr.id
-> group by id, doc ;
+----+------+-------+---------+
| id | data | doc | value12 |
+----+------+-------+---------+
| 2 | B | DOC 2 | 4000 |
| 3 | C | DOC 3 | 4000 |
| 3 | C | DOC 4 | 6000 |
+----+------+-------+---------+
3 rows in set (0.00 sec)
Sekarang gabungkan SUMMARY dari TRANSAKSI pertama dan kedua, menggunakan LEFT JOIN menjadi seperti ini.
mysql> select res1.id, res1.data, res1.doc
-> , value1
-> , value2
-> from (
-> select tm.id, data, doc, sum(value) value1
-> from t_master tm
-> join t_transaksi_1 tr on tm.id=tr.id
-> group by id, doc
-> ) res1
-> left join (
-> select tm.id, data, doc, sum(value) value2
-> from t_master tm
-> join t_transaksi_2 tr on tm.id=tr.id
-> group by id, doc
-> ) res2 on res1.id=res2.id and res1.doc=res2.doc ;
+----+------+-------+--------+--------+
| id | data | doc | value1 | value2 |
+----+------+-------+--------+--------+
| 1 | A | DOC 1 | 3000 | NULL |
| 2 | B | DOC 2 | 5000 | 4000 |
| 3 | C | DOC 3 | 3000 | 4000 |
| 3 | C | DOC 4 | 5000 | 6000 |
+----+------+-------+--------+--------+
4 rows in set (0.00 sec)
Namun masih ada yang sedikit aneh, karena ada nilai NULL di sana, maka kita gunakan perintah IFNULL() untuk mengunbahknya menjadi angka 0 (nol).
mysql> select res1.id, res1.data, res1.doc
-> , ifnull(value1,0) value1
-> , ifnull(value2,0) value2
-> from (
-> select tm.id, data, doc, sum(value) value1
-> from t_master tm
-> join t_transaksi_1 tr on tm.id=tr.id
-> group by id, doc
-> ) res1
-> left join (
-> select tm.id, data, doc, sum(value) value2
-> from t_master tm
-> join t_transaksi_2 tr on tm.id=tr.id
-> group by id, doc
-> ) res2 on res1.id=res2.id and res1.doc=res2.doc ;
+----+------+-------+--------+--------+
| id | data | doc | value1 | value2 |
+----+------+-------+--------+--------+
| 1 | A | DOC 1 | 3000 | 0 |
| 2 | B | DOC 2 | 5000 | 4000 |
| 3 | C | DOC 3 | 3000 | 4000 |
| 3 | C | DOC 4 | 5000 | 6000 |
+----+------+-------+--------+--------+
4 rows in set (0.00 sec)
Demikian TUTORIAL ini semoga membantu memahami penggunaan SUBQUERY, JOIN, GROUP BY, dan fungsi agregate SUM.