Sum grand total not returning the actual result?

I wrote a query to fetch an orders within a specific date range and display the sum of grand total. For example:

    // $fromDate = Carbon::createFromFormat('d-m-Y', $parameters['from_date_range'])->format('Y-m-d');
        // $toDate = Carbon::createFromFormat('d-m-Y', $parameters['to_date_range'])->format('Y-m-d');
        $fromDate = Carbon::createFromFormat('d-m-Y H:i:s', $parameters['from_date_range'] . ' 00:00:00')->toDateTimeString();
        $toDate = Carbon::createFromFormat('d-m-Y H:i:s', $parameters['to_date_range'] . ' 23:59:59')->toDateTimeString();
        
        //dd($fromDate);
        $brandIds = $parameters['brand_id'];

        if (in_array('all', $parameters['brand_id'])) {
            // Replace 'all' with an array of all available brand IDs
            $parameters['brand_id'] = Brand::whereNull('parent_id')->pluck('id')->toArray();
            
            $brandIds = $parameters['brand_id'];
        } 
        
        $brandNames = Brand::whereIn('id', $brandIds)->pluck('name')->toArray();
        $brandName = implode(', ', $brandNames);


        $statisticQuery = Order::join('members', 'orders.member_id', '=', 'members.id')
                ->join('brands', 'brands.id', '=', 'members.brand_id')
                ->join('member_monthly_sync_hdr', 'member_monthly_sync_hdr.brand_id', '=', 'brands.id')
                ->whereIn('members.brand_id', $parameters['brand_id'])
                ->whereIn('orders.status', [1, 2, 3])
                ->whereBetween('orders.created_at', [$fromDate, $toDate]);

                $statisticQuery->selectRaw("SUM(grand_total) as vip_points, 
                                concat('$fromDate', ' - ', '$toDate') as date_range, 
                                '$brandName' as brand_name, 
                                count(distinct(orders.member_id)) as unique_member_count,
                                null as deposit_amount,
                                null as deposit_count,
                                null as unique_deposit_count,
                                null as valid_bet,
                                null as withdraw_amount,
                                null as withdraw_count,
                                null as profit,
                                null as vip_retention_rate");
            }
            
            $statistic = $statisticQuery->get();
            dd($statistic);

I noticed it does not sum the grand total correctly. For example the grand_total column in my table is data type of decimal(10,2) and has value of 300.00 but when I die dump the data, it returns 4200.00. i tried getting the sql query from this query like dd($statisticQuery->toSql() and it returns sql query correctly like:

"""
select SUM(grand_total) as vip_points, 
                                concat('2023-03-08 00:00:00', ' - ', '2023-03-08 23:59:59') as date_range, 
                                'xxx' as brand_name, 
                                count(distinct(orders.member_id)) as unique_member_count,
                                null as deposit_amount,
                                null as deposit_count,
                                null as unique_deposit_count,
                                null as valid_bet,
                                null as withdraw_amount,
                                null as withdraw_count,
                                null as profit,
                                null as vip_retention_rate from `orders` inner join `members` on `orders`.`member_id` = `members`.`id` inner join `brands` on `brands`.`id` = `members`.`brand_id` inner join `member_monthly_sync_hdr` on `member_monthly_sync_hdr`.`brand_id` = `brands`.`id` where `members`.`brand_id` in (?, ?, ?, ?, ?, ?, ?) and `orders`.`status` in (?, ?, ?) and `orders`.`created_at` between ? and ? and `orders`.`deleted_at` is null
"""
I am not sure what went wrong as to why it does not return the grand total correctly. Does anyone have any idea?

LaryAI

3 years ago

Level 58

It seems like the issue might be with the data type of the grand_total column in your database table. Since it is a decimal with a precision of 10 and a scale of 2, it can store values up to 9999999.99. However, if the value exceeds this limit, it might cause unexpected results.

One way to fix this issue is to cast the grand_total column to a float or double data type in your query. You can do this by adding the following line before the selectRaw method:

$statisticQuery->selectRaw("CAST(SUM(grand_total) AS DOUBLE) as vip_points, ...");

This will cast the sum of grand_total to a double data type, which can handle larger values than decimal data type.

Alternatively, you can try changing the data type of the grand_total column in your database table to a float or double data type, depending on your requirements.

Hope this helps!

Please or to participate in this conversation.