How do I use a groupBy() with join in laravel 5

I have a little challenge here, I want to get orders from my orders table grouping by created_at field and aggregating the amount for each date. The amount is from another table (products) called price. Order table has a product_id as foreign key. I then did this

        $ord = DB::table('orders')
            ->join('products', 'orders.product_id', '=', 'products.id')
            ->select(DB::raw('DATE(orders.created_at) as created_at'),
                'products.price',
                //DB::raw('sum(products.price) as amount')
            )->orderBy('orders.created_at', 'desc')
            ->get();
        $ord->groupBy('created_at');
        return $ord;

The issue here is that when I uncomment the //DB::raw('sum(products.price) as amount'), it throws me an error Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column. Without it, I have a result like this

[{"created_at":"2020-05-26","price":"950"},{"created_at":"2020-05-18","price":"4000"},{"created_at":"2020-05-18","price":"4000"},{"created_at":"2020-05-12","price":"320"}]

But from here, two records on the 18th with 4000 should aggregate to 8000 for that date alone, that's how I want it. Any useful help on this will be greatly appreciated

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2X2x535
via IFTTT