I’m on Windows 10, MySQL Workbench. I’m on MySQL 8.0.
Here’s the dataset. northwind.sql.
Objective:
Write a query to get the order ID, customer’s name, grand total of each order, and the name of the employee who handled each order. See below the expected first four rows.
Output should look like this.
This is the database schema diagram.
It’s northwind.sql database.
This is my query.
SELECT
o.orderid,
c.contactname,
SUM(od.unitprice * od.quantity),
CONCAT(e.lastname, ' ', e.firstname) AS emp_name
FROM
orders o
INNER JOIN
customers c ON o.customerid = c.customerid
INNER JOIN
orderdetails od ON o.orderid = od.OrderID
INNER JOIN
employees e ON o.EmployeeID = e.EmployeeID
GROUP BY emp_name
ORDER BY orderid
LIMIT 4;
But it was producing an error.
Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘northwind.o.OrderID’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
But if I order by orderid alone, it’ll work, Why does it work? Should not it be producing the same error as above as not all non-aggregated query aren’t present in group by clause ?
What’s the computer science(Database concept) behind all this? Can anyone explain it?