Tanya: Field Saldo di Mysql Kombinasi dengan GROUP BY

her1z_n1a

Saya memiliki struktur tabel yang sedikit tidak biasa karena menggunakan banyak relasi UNION ALL untuk menghasilkan tampilan tabel seperti konsep mutasi rekening.

sebelumnya saya sudah sukses membuat perhitungan agregasi SALDO untuk tiap mutasi.

Saat ini menemukan masalah ketika diperlukan untuk Men-GROUP BY field tertentu dan rentang waktu tertentu.

//script menampilkan mutasi rekening awal

SELECT tmutasi.id, tmutasi.debit, tmutasi.kredit, @saldo_mutasi:=@saldo_mutasi+(tmutasi.debit)-(tmutasi.kredit) as saldo_mutasi, tmutasi.virtual_account, tmutasi.kegiatan, tmutasi.nama_akun, tmutasi.fungsi_detail, tmutasi.tgl_transaksi FROM ((SELECT '' AS id, '0' AS debit, '0' AS kredit,'26464238913' AS saldo, '' AS virtual_account, 'SALDO AWAL 2018-07-01' AS kegiatan, '' AS nama_akun, '' AS fungsi_detail, '2018-07-01' AS tgl_transaksi) UNION ALL (SELECT tgabungsemua.id AS id, tgabungsemua.debit AS debit, tgabungsemua.kredit AS kredit, '0' AS saldo, tgabungsemua.virtual_account AS virtual_account, tgabungsemua.kegiatan AS kegiatan, tgabungsemua.nama_akun AS nama_akun, tgabungsemua.fungsi_detail AS fungsi_detail, tgabungsemua.tgl_transaksi AS tgl_transaksi FROM ( (SELECT tdebit.id AS id, tdebit.setoran AS debit, '0' AS kredit, '0' AS saldo, tdebit.virtual_account AS virtual_account, tdebit.kegiatan AS kegiatan, tdebit.deskripsi AS deskripsi, tdebit.nama_akun AS nama_akun, tdebit.fungsi_detail AS fungsi_detail, tdebit.tgl_transaksi AS tgl_transaksi FROM ( (SELECT t_setoran.id_setoran AS id, t_setoran.payment_amount AS setoran, t_fungsi_detail.id_fungsi_detail AS id_fungsi_detail, t_fungsi_detail.fungsi_detail AS fungsi_detail, t_mitra.virtual_account AS virtual_account, t_akun_pendapatan_kerjasama.nama_akun AS nama_akun, t_mitra.description AS kegiatan, t_akun_pendapatan_kerjasama.deskripsi AS deskripsi, t_setoran.datetime_payment AS tgl_transaksi FROM t_setoran INNER JOIN t_mitra on t_setoran.trx_id=t_mitra.trx_id INNER JOIN t_join_mitra_akun on t_mitra.virtual_account=t_join_mitra_akun.virtual_account INNER JOIN t_akun_pendapatan_kerjasama on t_join_mitra_akun.id_akun_pendapatan_kerjasama=t_akun_pendapatan_kerjasama.id_akun_pendapatan_kerjasama INNER JOIN t_join_mitra_fungsi on t_join_mitra_akun.virtual_account=t_join_mitra_fungsi.virtual_account INNER JOIN t_fungsi_detail on t_join_mitra_fungsi.id_fungsi_detail=t_fungsi_detail.id_fungsi_detail) UNION ALL (SELECT t_setoran_giro.id_setoran_giro AS id, t_setoran_giro.jumlah_setoran AS setoran, t_fungsi_detail.id_fungsi_detail AS id_fungsi_detail, t_fungsi_detail.fungsi_detail AS fungsi_detail, t_ref_giro.customer_name AS virtual_account, t_akun_pendapatan_kerjasama.nama_akun AS nama_akun, t_setoran_giro.kegiatan AS kegiatan, t_akun_pendapatan_kerjasama.deskripsi AS deskripsi, t_setoran_giro.tgl_masuk AS tgl_transaksi FROM t_setoran_giro INNER JOIN t_join_mitra_akun on t_setoran_giro.id_setoran_giro=t_join_mitra_akun.virtual_account INNER JOIN t_akun_pendapatan_kerjasama on t_join_mitra_akun.id_akun_pendapatan_kerjasama=t_akun_pendapatan_kerjasama.id_akun_pendapatan_kerjasama INNER JOIN t_join_mitra_fungsi on t_join_mitra_akun.virtual_account=t_join_mitra_fungsi.virtual_account INNER JOIN t_fungsi_detail on t_join_mitra_fungsi.id_fungsi_detail=t_fungsi_detail.id_fungsi_detail INNER JOIN t_ref_giro on t_setoran_giro.id_ref_giro=t_ref_giro.id_ref_giro) ) AS tdebit) UNION ALL (SELECT tkredit.id, '0' AS debit, tkredit.jumlah_pencairan AS kredit, '0' AS saldo, tkredit.virtual_account AS virtual_account, tkredit.kegiatan AS kegiatan, tkredit.deskripsi AS deskripsi, tkredit.nama_akun AS nama_akun, tkredit.fungsi_detail AS fungsi_detail, tkredit.tgl_transaksi AS tgl_transaksi FROM ((SELECT t_pencairan.id_pencairan AS id, t_pencairan.jumlah_pencairan AS jumlah_pencairan, t_fungsi_detail.id_fungsi_detail AS id_fungsi_detail, t_fungsi_detail.fungsi_detail AS fungsi_detail, t_pencairan.keterangan AS kegiatan, t_mitra.virtual_account AS virtual_account, t_akun_pendapatan_kerjasama.deskripsi AS deskripsi, t_akun_pendapatan_kerjasama.nama_akun AS nama_akun, t_pencairan.tgl_pencairan AS tgl_transaksi FROM t_pencairan INNER JOIN t_mitra on t_pencairan.trx_id=t_mitra.trx_id INNER JOIN t_join_mitra_akun on t_mitra.virtual_account=t_join_mitra_akun.virtual_account INNER JOIN t_akun_pendapatan_kerjasama on t_join_mitra_akun.id_akun_pendapatan_kerjasama=t_akun_pendapatan_kerjasama.id_akun_pendapatan_kerjasama INNER JOIN t_join_mitra_fungsi on t_join_mitra_akun.virtual_account=t_join_mitra_fungsi.virtual_account INNER JOIN t_fungsi_detail on t_join_mitra_fungsi.id_fungsi_detail=t_fungsi_detail.id_fungsi_detail WHERE t_pencairan.id_asal_dana='1') UNION ALL (SELECT t_pencairan.id_pencairan AS id, t_pencairan.jumlah_pencairan AS jumlah_pencairan, t_fungsi_detail.id_fungsi_detail AS id_fungsi_detail, t_fungsi_detail.fungsi_detail AS fungsi_detail, t_pencairan.keterangan AS kegiatan, t_ref_giro.customer_name AS customer_name, t_akun_pendapatan_kerjasama.deskripsi AS deskripsi, t_akun_pendapatan_kerjasama.nama_akun AS nama_akun, t_pencairan.tgl_pencairan AS tgl_transaksi FROM t_pencairan INNER JOIN t_setoran_giro on t_pencairan.trx_id=t_setoran_giro.id_setoran_giro INNER JOIN t_join_mitra_akun on t_setoran_giro.id_setoran_giro=t_join_mitra_akun.virtual_account INNER JOIN t_akun_pendapatan_kerjasama on t_join_mitra_akun.id_akun_pendapatan_kerjasama=t_akun_pendapatan_kerjasama.id_akun_pendapatan_kerjasama INNER JOIN t_join_mitra_fungsi on t_join_mitra_akun.virtual_account=t_join_mitra_fungsi.virtual_account INNER JOIN t_fungsi_detail on t_join_mitra_fungsi.id_fungsi_detail=t_fungsi_detail.id_fungsi_detail INNER JOIN t_ref_giro on t_setoran_giro.id_ref_giro=t_ref_giro.id_ref_giro WHERE t_pencairan.id_asal_dana='2') ) AS tkredit) ) tgabungsemua )) tmutasi JOIN ( SELECT @saldo_mutasi:= '26464238913' ) saldo_mutasi WHERE tmutasi.tgl_transaksi BETWEEN '2018-07-01' AND '2018-07-15' ORDER BY tmutasi.tgl_transaksi,tmutasi.nama_akun ASC

/* hasil penampakkan */

her1z_n1a

length tidak cukup --
lanjutan pertanyaan

Untuk memudahkan melihat kesalahan yang ada berikut ini tampilan record terakhir (lihat field saldo mutasi)



mendapatkan hasil saldo mutasi dengan GROUP BY field nama_akun

SELECT tmutasi.id, sum(tmutasi.debit), sum(tmutasi.kredit), @saldo_mutasi:=@saldo_mutasi+sum(tmutasi.debit)-sum(tmutasi.kredit) as saldo_mutasi, tmutasi.virtual_account, tmutasi.kegiatan, tmutasi.nama_akun, tmutasi.fungsi_detail, tmutasi.tgl_transaksi FROM ((SELECT '' AS id, '0' AS debit, '0' AS kredit,'26464238913' AS saldo, '' AS virtual_account, 'SALDO AWAL 2018-07-01' AS kegiatan, '' AS nama_akun, '' AS fungsi_detail, '2018-07-01' AS tgl_transaksi) UNION ALL (SELECT tgabungsemua.id AS id, tgabungsemua.debit AS debit, tgabungsemua.kredit AS kredit, '0' AS saldo, tgabungsemua.virtual_account AS virtual_account, tgabungsemua.kegiatan AS kegiatan, tgabungsemua.nama_akun AS nama_akun, tgabungsemua.fungsi_detail AS fungsi_detail, tgabungsemua.tgl_transaksi AS tgl_transaksi FROM ( (SELECT tdebit.id AS id, tdebit.setoran AS debit, '0' AS kredit, '0' AS saldo, tdebit.virtual_account AS virtual_account, tdebit.kegiatan AS kegiatan, tdebit.deskripsi AS deskripsi, tdebit.nama_akun AS nama_akun, tdebit.fungsi_detail AS fungsi_detail, tdebit.tgl_transaksi AS tgl_transaksi FROM ( (SELECT t_setoran.id_setoran AS id, t_setoran.payment_amount AS setoran, t_fungsi_detail.id_fungsi_detail AS id_fungsi_detail, t_fungsi_detail.fungsi_detail AS fungsi_detail, t_mitra.virtual_account AS virtual_account, t_akun_pendapatan_kerjasama.nama_akun AS nama_akun, t_mitra.description AS kegiatan, t_akun_pendapatan_kerjasama.deskripsi AS deskripsi, t_setoran.datetime_payment AS tgl_transaksi FROM t_setoran INNER JOIN t_mitra on t_setoran.trx_id=t_mitra.trx_id INNER JOIN t_join_mitra_akun on t_mitra.virtual_account=t_join_mitra_akun.virtual_account INNER JOIN t_akun_pendapatan_kerjasama on t_join_mitra_akun.id_akun_pendapatan_kerjasama=t_akun_pendapatan_kerjasama.id_akun_pendapatan_kerjasama INNER JOIN t_join_mitra_fungsi on t_join_mitra_akun.virtual_account=t_join_mitra_fungsi.virtual_account INNER JOIN t_fungsi_detail on t_join_mitra_fungsi.id_fungsi_detail=t_fungsi_detail.id_fungsi_detail) UNION ALL (SELECT t_setoran_giro.id_setoran_giro AS id, t_setoran_giro.jumlah_setoran AS setoran, t_fungsi_detail.id_fungsi_detail AS id_fungsi_detail, t_fungsi_detail.fungsi_detail AS fungsi_detail, t_ref_giro.customer_name AS virtual_account, t_akun_pendapatan_kerjasama.nama_akun AS nama_akun, t_setoran_giro.kegiatan AS kegiatan, t_akun_pendapatan_kerjasama.deskripsi AS deskripsi, t_setoran_giro.tgl_masuk AS tgl_transaksi FROM t_setoran_giro INNER JOIN t_join_mitra_akun on t_setoran_giro.id_setoran_giro=t_join_mitra_akun.virtual_account INNER JOIN t_akun_pendapatan_kerjasama on t_join_mitra_akun.id_akun_pendapatan_kerjasama=t_akun_pendapatan_kerjasama.id_akun_pendapatan_kerjasama INNER JOIN t_join_mitra_fungsi on t_join_mitra_akun.virtual_account=t_join_mitra_fungsi.virtual_account INNER JOIN t_fungsi_detail on t_join_mitra_fungsi.id_fungsi_detail=t_fungsi_detail.id_fungsi_detail INNER JOIN t_ref_giro on t_setoran_giro.id_ref_giro=t_ref_giro.id_ref_giro) ) AS tdebit) UNION ALL (SELECT tkredit.id, '0' AS debit, tkredit.jumlah_pencairan AS kredit, '0' AS saldo, tkredit.virtual_account AS virtual_account, tkredit.kegiatan AS kegiatan, tkredit.deskripsi AS deskripsi, tkredit.nama_akun AS nama_akun, tkredit.fungsi_detail AS fungsi_detail, tkredit.tgl_transaksi AS tgl_transaksi FROM ((SELECT t_pencairan.id_pencairan AS id, t_pencairan.jumlah_pencairan AS jumlah_pencairan, t_fungsi_detail.id_fungsi_detail AS id_fungsi_detail, t_fungsi_detail.fungsi_detail AS fungsi_detail, t_pencairan.keterangan AS kegiatan, t_mitra.virtual_account AS virtual_account, t_akun_pendapatan_kerjasama.deskripsi AS deskripsi, t_akun_pendapatan_kerjasama.nama_akun AS nama_akun, t_pencairan.tgl_pencairan AS tgl_transaksi FROM t_pencairan INNER JOIN t_mitra on t_pencairan.trx_id=t_mitra.trx_id INNER JOIN t_join_mitra_akun on t_mitra.virtual_account=t_join_mitra_akun.virtual_account INNER JOIN t_akun_pendapatan_kerjasama on t_join_mitra_akun.id_akun_pendapatan_kerjasama=t_akun_pendapatan_kerjasama.id_akun_pendapatan_kerjasama INNER JOIN t_join_mitra_fungsi on t_join_mitra_akun.virtual_account=t_join_mitra_fungsi.virtual_account INNER JOIN t_fungsi_detail on t_join_mitra_fungsi.id_fungsi_detail=t_fungsi_detail.id_fungsi_detail WHERE t_pencairan.id_asal_dana='1') UNION ALL (SELECT t_pencairan.id_pencairan AS id, t_pencairan.jumlah_pencairan AS jumlah_pencairan, t_fungsi_detail.id_fungsi_detail AS id_fungsi_detail, t_fungsi_detail.fungsi_detail AS fungsi_detail, t_pencairan.keterangan AS kegiatan, t_ref_giro.customer_name AS customer_name, t_akun_pendapatan_kerjasama.deskripsi AS deskripsi, t_akun_pendapatan_kerjasama.nama_akun AS nama_akun, t_pencairan.tgl_pencairan AS tgl_transaksi FROM t_pencairan INNER JOIN t_setoran_giro on t_pencairan.trx_id=t_setoran_giro.id_setoran_giro INNER JOIN t_join_mitra_akun on t_setoran_giro.id_setoran_giro=t_join_mitra_akun.virtual_account INNER JOIN t_akun_pendapatan_kerjasama on t_join_mitra_akun.id_akun_pendapatan_kerjasama=t_akun_pendapatan_kerjasama.id_akun_pendapatan_kerjasama INNER JOIN t_join_mitra_fungsi on t_join_mitra_akun.virtual_account=t_join_mitra_fungsi.virtual_account INNER JOIN t_fungsi_detail on t_join_mitra_fungsi.id_fungsi_detail=t_fungsi_detail.id_fungsi_detail INNER JOIN t_ref_giro on t_setoran_giro.id_ref_giro=t_ref_giro.id_ref_giro WHERE t_pencairan.id_asal_dana='2') ) AS tkredit) ) tgabungsemua )) tmutasi JOIN ( SELECT @saldo_mutasi:= '26464238913' ) saldo_mutasi WHERE tmutasi.tgl_transaksi BETWEEN '2018-07-01' AND '2018-07-15' GROUP BY tmutasi.nama_akun ORDER BY tmutasi.tgl_transaksi,tmutasi.nama_akun ASC

Hasil penampakkan query nya



Mari lihat record terakhir saldo mutasi tidak sama setelah dilakukan GROUP BY




Mohon solusi dan saran barangkali ada yang pernah mengalami case serupa. Barangkali ada yang terlewat tidak saya lakukan.

Terima kasih,