[FIXED] How can I write this SQL query in Laravel by Left Join, count and group by?

Issue

The database has "orders" and "customers" tables. I can't connect them to this usgbu request.

SELECT orders.serial_number, 
count(orders.order_id) as count , 
customers.customer_name FROM orders 
LEFT JOIN customers ON 
orders.customer_id = customers.customer_id 
GROUP By orders.serial_number;

The code I wrote is as follows:

$orders = Orders::select("orders.serial_number","customers.customer_name", DB::raw("COUNT(orders.order_id) as count"))
        ->leftJoin('customers','orders.customer_id','=','customers.customer_id')
        ->groupBy('orders.serial_number')
        ->get();

An error message appears as follows:

SQLSTATE[42000]: Syntax error or access violation: 1055 'alqorshop.customers.customer_name' isn't in GROUP BY (SQL: select `orders`.`serial_number`, `customers`.`customer_name`, COUNT(orders.order_id) as count from `orders` left join `customers` on `orders`.`customer_id` = `customers`.`customer_id` group by `orders`.`serial_number`)

Solution

The correct query for this sql is:

    SELECT orders.serial_number,
    customers.customer_name, 
    count(orders.order_id) as count
    FROM orders 
    LEFT JOIN customers ON 
    orders.customer_id = customers.customer_id 
    GROUP By orders.serial_number,customers.customer_name;

OR

    SELECT orders.serial_number,    
    count(orders.order_id) as count
    FROM orders     
    LEFT JOIN customers ON 
    orders.customer_id = customers.customer_id 
    GROUP By orders.serial_number;

You did not include "customers.customer_name" in your group by clause which is why it's giving an error. Either add customers.customer_name to your group by query or remove customers.customer_name from select statement

Answered By - Chirag Patankar

Answer Checked By - Mildred Charles (PHPFixing Admin)