0

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.

enter image description here 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?

4
  • 1
    Is there a way I can permanently disable sql_mode=only_full_group_by? Formally this is possible. But this is not correct solution. You must backwardly to edit your queries and make them correct.
    – Akina
    Jun 9, 2023 at 6:36
  • 2
    ref Why should not disable ONLY_FULL_GROUP_BY,
    – danblack
    Jun 9, 2023 at 6:37
  • If a column not mentioned in GROUP BY is not aggregated: case 1 - all rows in a group have the same value in this column, simply add it to GORUP BY expression; case2 - there are differrent values in this column, w/o only_full_group_by some indefinite one of them will be returned, and in this case you do not need in this column value at all, so remove it from the output list. As a palliative you may use special aggregate function ANY_VALUE().
    – Akina
    Jun 9, 2023 at 6:37
  • @Akina can you explain a bit more, It's not clear to me.
    – achhainsan
    Jun 9, 2023 at 7:29

1 Answer 1

0

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.

4
  • >GROUP BY emp_name - obviously invalid, what if an employee handles more than one order. I don't understand what'd be wrong with this. Can you elaborate?
    – achhainsan
    Jun 9, 2023 at 7:13
  • >When joined on orderdetails, multiple entries are possible, however these go through the aggregate function sum so a single result occurs. How? I don't understand. It's joined on orderid which is a primary key as well.
    – achhainsan
    Jun 9, 2023 at 7:26
  • I've updated the question.
    – achhainsan
    Jun 9, 2023 at 11:16
  • 1) Is it necessary that expression in group by(expression) should be unique? 2) I didn't understand what you were trying to potray by those 3 bulleted points! Could you clarify. By now, I understand that if there are 2 orders with same emp_name, since order isn't aggregated which order would you take and that'd produce meaningless query. But how do I figure that beforehand? A framework to think?
    – achhainsan
    Jun 11, 2023 at 4:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.