CODEWITHSUNDEEP
phpmysql
Loren Ramly
Loren Ramly

Reputation: 1111

Mysql Query SUM field string and group by

I have a talbe name is laporan , current query is

"SELECT * FROM laporan"

and then if i print_r i get result data like this:

[0] => Array
    (
        [tanggal_laporan] => 2012-11-04
        [total_kas] => Rp 21.000,-
    )

[1] => Array
    (
        [tanggal_laporan] => 2012-11-04
        [total_kas] => Rp 50.500,-
    )

[2] => Array
    (
        [tanggal_laporan] => 2012-11-04
        [total_kas] => Rp 56.000,-
    )

[3] => Array
    (
        [tanggal_laporan] => 2012-11-05
        [total_kas] => Rp 89.000,-
    )

[4] => Array
    (
        [tanggal_laporan] => 2012-11-05
        [total_kas] => Rp 73.000,-
    )

I want to SUM field total_kas which this field is varchar/string, my question is there a way/can to SUM a field varchar/string. and then this query group_by tanggal_laporan, So i want to get data as below:

[0] => Array
    (
        [tanggal_laporan] => 2012-11-04
        [total_kas] => 127500
    )

[1] => Array
    (
        [tanggal_laporan] => 2012-11-05
        [total_kas] => 162000
    )

Thanks.

Upvotes: 0

Views: 1690

Answers (1)

Cynical
Cynical

Reputation: 9568

This should work (assuming that total_kas always has the form 'Rp NUMBER,-'):

SELECT tanggal_laporan, SUM(CAST(
    SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(total_kas, '.', ''), ' ', -1), ',', 1)
    AS UNSIGNED INTEGER)) AS num 
FROM laporan 
GROUP BY tanggal_laporan

I have done some changes to deal with integer numbers. Here is a SQLFiddle example. Remove the UNSIGNED is it doesn't suit your needs.

Upvotes: 1

Related Questions