How does SQL MAX() works?

MAX() will return only the maximum value for that column, it doesn't affect the other columns. To get your desired result you need to run the following:

SELECT name, AVG(quantity) as avgq 
FROM orders 
GROUP BY name
ORDER BY AVG(quantity) DESC
LIMIT 1;

According to the SQL standard, your outer query is not correct.

Because you use the aggregate function MAX() without a GROUP BY clause, an implicit GROUP BY () is assumed (both by the SQL99 standard and) by MySQL:

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

(source: 12.18.1 GROUP BY (Aggregate) Functions)

You can use t.name in the SELECT clause only if any of these happens:

• it also appears in the GROUP BY clause;
• it is used as an argument of a GROUP BY aggregate function;
• it is functionally dependent on a column that appears in the GROUP BY clause.

MySQL accepts queries whose SELECT fields do not follow the rules of GROUP BY enumerated above but, in this case, the values returned for these fields are indeterminate:

... a MySQL extension to the use of GROUP BY is to permit the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the (...) query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want.

(source: 12.18.3 MySQL Handling of GROUP BY)

A query that works

You can keep the inner query, order the resulting rows descending by AVG(quantity) and use another MySQL extension of the SQL standard (i.e. LIMIT) to get only the first value (which is the maximum after ordering).

The query is:

SELECT name, AVG(quantity) AS avgq 
FROM orders
GROUP BY name
ORDER BY avgg DESC
LIMIT 1