How does MySQL handles GROUP BY?

Question

I'm on Windows 10, MySQL Workbench. I'm on MySQL 8.0.

Here's the dataset. northwind.sql.

https://pastebin.com/bMgjXvfT

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.

https://brucebauer.info/assets/ITEC3610/Northwind/Northwind-Sample-Database-Diagram.pdf

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?

Answer 1

Start with basics, GROUP BY x will create a set of rows in which x is unique. The columns beside x in the rows of the result will be a single value. The other column results are the values listed in the first part of the select query. If by table structures, notably not by the data in the tables, the other result columns can only be a single value, it will be used. If its at all possible that multiple values of another column exist for x, and invalid aggregation error occurs. This can be rectified by applying a aggregate function over the value, and the function reduces the multiple values to a single value.

GROUP BY emp_name - if an employee handles more than one order, with the result set as o.orderid, isn't an aggregate expression. Group by emp_name means there will be one emp_name row in the result. With the possibility of more than one order for emp_name, without an aggregate expression over it, there cannot be one emp_name row and multiple orders with the same emp_name. If any employee handled more than one row, what would the column be? The answer is there's no defined value because the query cannot aggregate them into a single value.

GROUP BY orderid, regardless of which one (they are equivalent due to [inner] join), is a good aggregation and the best way for the query. orderid is the primary key of o/orders and therefore unique (like all primary keys).

• When joined on employees its on EmployeeID, the PK, so there will only be one employee per orderid, so there will always be one employee per order id in the result set and any employee columns can appear in the result.
• When joined on customers, by customerid, like employees, cutomerid is uniue, so there will be only one customer for the orderid and therefore any customer table columns can be in the result.
• When joined on orderdetails, multiple results per orderid are possible, however orderdetails.{quantity,unitcost} go through the aggregate function sum; so a single result occurs for orderid can is computed, and it can fit into a single value in the single row output for orderid.

Also be careful on group by {expression}. Even on the situation here emp_name, based on structure (not data), isn't defined as unique in the same way as EmployeeID. The unique relation between EmployeeID can be seen, but emp_name isn't necessary unique in employees.