Rolling SUM data MySQL

Rolling SUM digunakan untuk menjumlahkan suatu kolom – k0lom tertentu pada tabel. Seperti halnya yang terdapat pada rekening tabungan. Setiap row akan di kalkulasi di row selanjutnya. Dapat juga dikelompokkan berdasarkan kolom-kolom tertentu. Saya ingin mencoba untuk membuat syntax sederhana yang berfungsi sebagai rolling sum. Sebelum saya membuat syntaxnya, saya akan membuat dulu tabelnya. tabel ini saya beri nama acBalance.

mysql> CREATE TABLE acbalance (
    ->   id int(11) NOT NULL AUTO_INCREMENT,
    ->   accountID int(5) DEFAULT NULL,
    ->   acPost date DEFAULT NULL,
    ->   balance double(15,0) DEFAULT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO acbalance (accountID, acPost, balance) VALUES
    ->   (3, '2007-01-31', 50000),
    ->   (3, '2007-02-28', 30000),
    ->   (3, '2007-03-31', 10000),
    ->   (3, '2007-04-30', 60000),
    ->   (3, '2007-05-31', 30000),
    ->   (3, '2008-01-31', 50000),
    ->   (3, '2008-02-29', 30000),
    ->   (3, '2008-03-31', 90000),
    ->   (3, '2008-04-30', 30000),
    ->   (3, '2008-05-31', 50000);
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT b.accountName,
    ->   MONTHNAME(a.acPost) as MONTH,
    ->   a.balance
    ->   FROM
    ->   acbalance a JOIN
    ->   account b ON
    ->   a.accountID=b.accountID WHERE
    ->   acPost>'2008-01-01';

+-------------+----------+---------+
| accountName | MONTH    | balance |
+-------------+----------+---------+
| Kas         | January  |   50000 |
| Kas         | February |   30000 |
| Kas         | March    |   90000 |
| Kas         | April    |   30000 |
| Kas         | May      |   50000 |
+-------------+----------+---------+
5 rows in set (0.00 sec)

Sekilas memang tidak ada masalah dengan tabel diatas. Tabel diatas hanya sebagai contoh. Tapi bagaimana jika kita ingin menampilkan akumulasi jumlah dari bulan ke bulan pada tahun yang sama. Saya ambil contoh pada tahun 2007 terlebih dahulu. Untuk mendaptakan rolling sum, saya akan menggunakan sub query. Dua buah query akan akan dipakai untuk menghasilkan rolling sum. Query yang pertama untuk mendapatkan jumlah nominal balance, dan query yang kedua untuk mendapatkan rolling sum nya.

Query pertama :

mysql> SELECT b. accountName, MONTH(a.acPost) AS MonthNo,
    -> MONTHNAME(a.acPost) AS MonthName,
    -> a.balance AS saving
    -> FROM acbalance a, account b
    -> WHERE a.accountID=b.accountID AND
    -> a.acPost <'2008-01-01'
    -> GROUP BY MONTH(a.acPost);
+-------------+---------+-----------+---------+
| accountName | MonthNo | MonthName | Rolling |
+-------------+---------+-----------+---------+
| Kas         |       1 | January   |   50000 |
| Kas         |       2 | February  |   30000 |
| Kas         |       3 | March     |   10000 |
| Kas         |       4 | April     |   60000 |
| Kas         |       5 | May       |   30000 |
+-------------+---------+-----------+---------+
5 rows in set (0.00 sec)

Query kedua :

mysql> SELECT MONTH(acPost) AS MonthNo,
    -> MONTHNAME(acPost) AS MonthName,
    -> balance AS saving
    -> FROM acbalance
    -> WHERE acPost <'2008-01-01'
    -> GROUP BY MONTH(acPost);
+---------+-----------+---------+
| MonthNo | MonthName | Rolling |
+---------+-----------+---------+
|       1 | January   |   50000 |
|       2 | February  |   30000 |
|       3 | March     |   10000 |
|       4 | April     |   60000 |
|       5 | May       |   30000 |
+---------+-----------+---------+
5 rows in set (0.00 sec)

Memang hampir tidak ada bedanya antara query pertama dan query kedua. Hanya pada query pertama memunculkan nama account yang berelasi dengan tabel account. Dan kedua-duanya memang mencari jumlah nominal tiap bulan. Kemudian kita gabungkan kedua query tersebut dengan menggunakan kondisi INNER JOIN.

mysql> SELECT
    -> a.accountName,
    -> a.MonthName,
    -> a.saving,
    -> SUM(b.saving) AS lastBalance
    -> FROM
    -> (SELECT b.accountName, MONTH(a.acPost) AS MonthNo,
    -> MONTHNAME(a.acPost) AS MonthName,
    -> a.balance AS saving
    -> FROM acbalance a, account b
    -> WHERE a.accountID=b.accountID AND
    -> a.acPost <'2008-01-01'
    -> GROUP BY MONTH(a.acPost)
    -> ) AS a
    -> INNER JOIN (
    -> SELECT
    -> MONTH(acPost) AS MonthNo,
    -> MONTHNAME(acPost) AS MonthName,
    -> balance AS saving
    -> FROM acbalance
    -> WHERE acPost < '2008-01-01'
    -> GROUP BY MONTH(acPost)
    -> ) AS b
    -> ON a.MonthNo >= b.MonthNo
    -> GROUP BY a.MonthNo;

Dan inilah hasilnya, kita sudah mendapatkan rolling sum dari acBalance Kas mulai dari bulan Januari sampai May.

+-------------+-----------+--------+-------------+
| accountName | MonthName | saving | lastBalance |
+-------------+-----------+--------+-------------+
| Kas         | January   |  50000 |       50000 |
| Kas         | February  |  30000 |       80000 |
| Kas         | March     |  10000 |       90000 |
| Kas         | April     |  60000 |      150000 |
| Kas         | May       |  30000 |      180000 |
+-------------+-----------+--------+-------------+
5 rows in set (0.00 sec)

Bagaimana jika kita ingin menjumlahkan balance pada tahun 2008 tetapi juga mengambil data sum dari tahun 2007. Sebelumnya kita ambil dulu keseluruhan balance dari tahun 2007. Seperti ini :

mysql> SELECT SUM(balance) INTO @balance2007 FROM acbalance
    -> WHERE acPost < '2008-01-01';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @balance2007;
+--------------+
| @balance2007 |
+--------------+
| 180000       |
+--------------+
1 row in set (0.00 sec)

Kemudian kita gabungkan dengan Query dibawah ini :

mysql> SELECT
    -> a.MonthNo,
    -> a.MonthName,
    -> a.saving,
    -> SUM(b.lastBalance)+@balance2007 AS lastBalance
    -> FROM
    -> (SELECT
    -> MONTH(acPost) AS MonthNo,
    -> MONTHNAME(acPost) AS MonthName,
    -> balance AS saving
    -> FROM acBalance
    -> WHERE acPost >= '2008-01-01'
    -> GROUP BY MONTH(acPost)) AS a
    -> INNER JOIN (
    -> SELECT
    -> MONTH(acPost) AS MonthNo,
    -> MONTHNAME(acPost) AS MonthName,
    -> balance AS lastBalance
    -> FROM acbalance
    -> WHERE acPost >= '2008-01-01'
    -> GROUP BY MONTH(acPost)) AS b
    -> ON a.MonthNo >= b.MonthNo
    -> GROUP BY a.MonthNo;
+---------+-----------+--------+-------------+
| MonthNo | MonthName | saving | lastBalance |
+---------+-----------+--------+-------------+
|       1 | January   |  50000 |      230000 |
|       2 | February  |  30000 |      260000 |
|       3 | March     |  90000 |      350000 |
|       4 | April     |  30000 |      380000 |
|       5 | May       |  50000 |      430000 |
+---------+-----------+--------+-------------+
5 rows in set (0.00 sec)
mysql> SELECT SUM(balance) as lastBalance FROM acbalance;
+-------------+
| lastBalance |
+-------------+
|      430000 |
+-------------+
1 row in set (0.00 sec)

Hasil dari SubQuery dan query diatas sama hasilnya. Postingan ini jauh dari sempurna. Masih banyak modifikasi yang bisa dilakukan dengan kondisi-kondisi tertentu. Saya harap jika anda membaca tulisan ini, anda dapat mengembangkannya lebih baik lagi.šŸ˜€

6 Responses to “Rolling SUM data MySQL”

  1. yanto Says:

    Bagaimana kalau saya ringkas menjadi begini:

    select a.accountID, a.acPost, a.balance, sum(b.balance) as LastBalance
    from acbalance a join acbalance b on a.acPost>=b.acPost
    where a.acPost>=’2008-01-01′
    group by a.accountID, a.acPost, a.balance

    Hasilnya seperti query yang terakhir. Boleh nggak?

  2. ridwan Says:

    Boleh mas.. hasilnya pun juga sama..
    Query diatas memang jauh dari sempurna, thanks atas ringkasannya.šŸ™‚

  3. rofans Says:

    mau nanya itu strukturnya tabel account gimana ya??

  4. nedi Says:

    MAS… saya ada masalah nie…
    gini lho mas…!!!

    saya lagi buat program KHS mahasiswa persemesternya,, menggunakan database MySQL,,
    jd, tabel di tiap semester saya pisah,,
    misalnya tabel semester 1, tabel semester 2, tabel semester 3, n strusnya…
    di tiap tablenya ada NIM_mahasiswa, kode_matakuliah, dan nilai.
    nah klo SKS nya berada di tabel mata_kuliah, di dlam table mata_kuliah berisi kode_matakuliah, dan SKS,,
    yang saya mw tanyak mas,,??
    gmna caranya menggabungkan tabel_matakuliah, semester 1, semester 2,, untuk mencari nilai KHS…
    klo logikanya mencari IPK’nya mas…
    “IP semester 1 + IP semester 2 / jumlah SKS 1 + jumlah SKS 2.””
    tlong ya mas…
    saya lagi buat Tugas Akhir nie mas…..
    mas klo tw krim k email mas ya… mhd_nedi@yahoo.co.id.
    tlong ya mas…

  5. budi Says:

    mas ada tabel jurnal yang fieldny sebagai berikut :
    tgl-uraian-akun-possal-debet-kredit

    saya mau coba posting ke buku besar per akun dan perbulan dengan tambahan field saldo, saldo di isi dengan rumus jika possal=db maka saldo sebelumnya + debet – kredit, jika possal=kr maka saldo sebelumnya + kredit – debet
    jadi untuk jumlah saldo terakumulasi per transaksi, source kodenya gimana ya mas , aku pake mysql server 5.0 dan delphi 7

  6. budi Says:

    mas aku mau posting jurnal umum ke buku besar, contoh diatas saya sudah coba alhamdullillah berhasil, cuma saya masih bingung untuk saldo awal, misal saya tampilkan buku besar untuk bulan 2 tahun 2011 berarti saldo awal sebelum tanggal 1 pebruari 2011, cara menghitung saldo awal gimana dan menambahkan ke saldo bagaimana, kalau bisa dijelaskan source kode untuk runtimenya ya mas, saya pake mysql versi 5 dan delphi 7..
    mohon pencerahannya…..


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: