Common Issue with GROUP BY: "Expression is not in GROUP BY..."

As mentioned in the beginning of the course, you are likely to see a message like this:

Which will read something like:

Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column...

This happens due to the "strict mode" in MySQL. You can fix this by turning off the strict mode, but it is a bad option as it can lead to data corruption, so I won't even show you the syntax of it.

It's better to see if the column is needed in the GROUP BY clause or if it can be aggregated. For example:

$orders = Order::selectRaw(
    'month(orders.order_time) as month, sum(order_product.quantity) as total_quantity, sum(orders.total) as order_total, count(*) as total_orders, products.name as product_name'
)
    ->join('order_product', 'order_product.order_id', '=', 'orders.id')
    ->join('products', 'order_product.product_id', '=', 'products.id')
    ->groupByRaw('month(orders.order_time)')
    ->orderBy('month')
    ->orderBy('total_orders', 'desc')
    ->get();

This query lacks information on what to do with our product_name column. We can add it to the GROUP BY clause or delete it from our select if we don't need it. For example:

$orders = Order::selectRaw(
    'month(orders.order_time) as month, sum(order_product.quantity) as total_quantity, sum(orders.total) as order_total, count(*) as total_orders, products.name as product_name'
)
    ->join('order_product', 'order_product.order_id', '=', 'orders.id')
    ->join('products', 'order_product.product_id', '=', 'products.id')
    ->groupByRaw('month(orders.order_time)')
    ->groupByRaw('month(orders.order_time), product_name')
    ->orderBy('month')
    ->orderBy('total_orders', 'desc')
    ->get();