Rolling SUM data MySQL – Tabungan


terinspirasi oleh tulisan yaya_retina and special thanks 2 yanto

Rolling SUM ialah suatu teknik penjumlahan yang berkelanjutan dari satu baris record kemudian ditambah kembali dengan data record selanjutnya. Implementasinya seperti buku tabungan waktu kita SD dulu… hehehhe
Ilustrasi ….
+————+———+————-+
| Tanggal | Nominal | Total   |
+————+———+————-+
| 2007-01-31 | 50000 | 50000 |
| 2007-02-28 | 30000 | 80000 | <—– 5000 + 3000 = 8000
| 2007-03-31 | 10000 | 90000 | <—– 8000 + 10000 = 18000
| 2007-04-30 | 60000 | 150000 |<—– dst
| 2007-05-31 | 30000 | 180000 |
| 2008-01-31 | 50000 | 230000 |
| 2008-02-29 | 30000 | 260000 |
| 2008-03-31 | 90000 | 350000 |
| 2008-04-30 | 30000 | 380000 |
| 2008-05-31 | 50000 | 430000 |
+————+———+————-+

Untuk eksprerimen anda butuh beberapa tabel and data berikut :

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 AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

/*Data for the table `acbalance` */

insert  into `acbalance`(`id`,`accountID`,`acPost`,`balance`) values

(1,3,’2007-01-31′,50000),(2,3,’2007-02-28′,30000),

(3,3,’2007-03-31′,10000),(4,3,’2007-04-30′,60000),

(5,3,’2007-05-31′,30000),(6,3,’2008-01-31′,50000),

(7,3,’2008-02-29′,30000),(8,3,’2008-03-31′,90000),

(9,3,’2008-04-30′,30000),(10,3,’2008-05-31′,50000);

Query :

select a.accountID, a.acPost, a.balance, sum(b.balance) as LastBalance
from acbalance a join acbalance b on a.acPost>=b.acPost
group by a.accountID,a.acPost,a.balance

Hasil :

rool sum mysql

About blackphp

All about programming php, ajax, jquery, mysql, etc, tips n trick. Now, i'm concern in php programming with codeigniter framework n also training my design skill with photoshop or corel... wish me luck! ('o')

Posted on October 25, 2009, in mYsQL and tagged , . Bookmark the permalink. 7 Comments.

  1. Bagus Artikelnya mas, sangat membantu, tapi saya mau tanya, kalo yang nyari jeda antar tanggal gimana, misal :

    24/11/2008 0
    26/11/2008 2
    30/11/2008 4

    mohon bantuannnya mas,, kirim e-mail.

    • apakah hasilnya akan dijumlahkan??
      Anda bisa coba query ini…

      SELECT * FROM tabel_dt WHERE tanggal BETWEEN ’24/11/2008′ AND ’30/11/2008′

  2. mantap mas…lanjutkan..heheh

  3. iya mas ak juga mau tanya gimana jika punya data spt :
    Tanggal
    24/11/2008 5
    25/11/2008 6
    26/11/2008 3
    27/11/2008 5
    28/11/2008 4

    lihat tanggal 26 s/d. 28
    hitung dulu tanggal 24 & 25 = 11
    menjadi :
    11 3
    14 5
    19 4

  4. silahkan email saya aja.. ato chat saya di YM: linzyxer

  5. bila accounId’y berbeda dn tanggal’y sama gimn?

  6. @alfi : aq belum test.. sekilas bila ada accoun’id yg berbeda & jika digunakan query diatas. nilai akan rancu. Sehingga sebaiknya ditambahkan where account’id = ‘3’ (langsung per account’id)

    namun bila ingin menampilkan semua accound’d belum saya coba.😀

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: