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`)
CodePudding user response:
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
CodePudding user response:
Your query is not correct -- you need to add customers.customer_name
to the GROUP BY
clause or remove it from the SELECT
clause