I've problem with the result of sum(). please take a look at this var_dump()

                array(6) {
        [0]=>
        object(stdClass)#21 (5) {
            ["user_id"]=>
            string(2) "52"
            ["barang_id"]=>
            string(2) "18"
            ["jml_finish"]=>
            string(3) "120"
            ["harga_pasar"]=>
            string(6) "250000"
            ["jml_pendapatan_sales"]=>
            string(8) "30000000"
        }
        [1]=>
        object(stdClass)#23 (5) {
            ["user_id"]=>
            string(2) "54"
            ["barang_id"]=>
            string(2) "12"
            ["jml_finish"]=>
            string(3) "550"
            ["harga_pasar"]=>
            string(5) "25000"
            ["jml_pendapatan_sales"]=>
            string(8) "12500000"
        }
        [2]=>
        object(stdClass)#24 (5) {
            ["user_id"]=>
            string(2) "54"
            ["barang_id"]=>
            string(2) "18"
            ["jml_finish"]=>
            string(2) "50"
            ["harga_pasar"]=>
            string(6) "250000"
            ["jml_pendapatan_sales"]=>
            string(8) "12500000"
        }
        [3]=>
        object(stdClass)#25 (5) {
            ["user_id"]=>
            string(2) "54"
            ["barang_id"]=>
            string(2) "11"
            ["jml_finish"]=>
            string(3) "100"
            ["harga_pasar"]=>
            string(5) "80000"
            ["jml_pendapatan_sales"]=>
            string(7) "8000000"
        }
        [4]=>
        object(stdClass)#26 (5) {
            ["user_id"]=>
            string(2) "54"
            ["barang_id"]=>
            string(2) "19"
            ["jml_finish"]=>
            string(3) "100"
            ["harga_pasar"]=>
            string(5) "70000"
            ["jml_pendapatan_sales"]=>
            string(7) "7000000"
        }
        [5]=>
        object(stdClass)#27 (5) {
            ["user_id"]=>
            string(2) "54"
            ["barang_id"]=>
            string(2) "24"
            ["jml_finish"]=>
            string(2) "25"
            ["harga_pasar"]=>
            string(5) "55000"
            ["jml_pendapatan_sales"]=>
            string(7) "1375000"
        }
    }

as you can see, I tried to multiply

jml_finish * harga_pasar = jml_pendapatan_sales

and I think the second array is giving incorrect result, but the rest is correct.

here's my mysql code

        SELECT 
        
        user_id,
        barang_id,
        
        sum(kirim_barang_jml_finish) as jml_finish,
        max(table_barang.harga_pasar),
        max(table_barang.harga_pasar * kirim_barang_jml_finish) as jml_pendapatan_sales 
        
        from table_so_sales
        
        join table_barang on table_so_sales.barang_id = table_barang.id
        join table_user on table_so_sales.user_id = table_user.id
        join table_branch_lokasi on table_so_sales.branch_id_so = table_branch_lokasi.id
        
        WHERE kirim_barang_jml_finish > 0
        
        group by barang_id, user_id
        order by jml_pendapatan_sales desc

I tried to remove the max(), but still it gives incorrect value. Thank you in advance

EDIT 1 here's my table (more or less) :

a. table_so_sales

    id  barang_id   user_id     kirim_barang_jml_finish
    1       1              1        200
    2       2              1        200
    3       1              2        350

b. table_barang

   id   harga_pasar
    1   25000
    2   30000

c. table_user

    id  name
    1   Jhon
    2   Doe

p.s ignore table_branch_lokasi

CodePudding user response：

First you need to understand the difference between SUM() and MAX(). Both are aggregate functions and as per the documentation itself, it says

SUM() - Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used to sum only the distinct values of expr.

and

MAX() - Returns the maximum value of expr. MAX() may take a string argument; in such cases, it returns the maximum string value

Now, let's take your table_so_sales data to do some exercises:

First, let's do SUM(); particularly this query:

SELECT SUM(id), SUM(barang_id), SUM(user_id), SUM(kirim_barang_jml_finish)
FROM table_so_sales;

You'll get a result like this:

However, when doing MAX():

SELECT MAX(id), MAX(barang_id), MAX(user_id), MAX(kirim_barang_jml_finish)
FROM table_so_sales;

you'll get:

Let's say I simplified your query to something like this:

SELECT  tss.barang_id,
        SUM(kirim_barang_jml_finish) AS jml_finish,
        MAX(tb.harga_pasar) highest_price,
        MAX(tb.harga_pasar * kirim_barang_jml_finish) AS max1,
        SUM(tb.harga_pasar * kirim_barang_jml_finish) AS sum1,
        GROUP_CONCAT(tb.harga_pasar * kirim_barang_jml_finish) AS data_in_rows
        
FROM table_so_sales tss
JOIN table_barang tb ON tss.barang_id=tb.id
GROUP BY tss.barang_id

That will return a result like this:

As you can see, data in max1 and sum1 differs simply because MAX() is getting the highest value of tb.harga_pasar * kirim_barang_jml_finish result while SUM() is adding up all of the result.

Demo fiddle

Hope this will help you understand more.