Belajar SQL: Penggunaan SUM dan JOIN untuk membuat Laporan Summary

Belajar SQL: Penggunaan SUM dan JOIN untuk membuat Laporan Summary

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.