Group by Behavior When No Aggregate Functions Are Present in the Select Clause

GROUP BY behavior when no aggregate functions are present in the SELECT clause

Read MySQL documentation on this particular point.

In a nutshell, MySQL allows omitting some columns from the GROUP BY, for performance purposes, however this works only if the omitted columns all have the same value (within a grouping), otherwise, the value returned by the query are indeed indeterminate, as properly guessed by others in this post. To be sure adding an ORDER BY clause would not re-introduce any form of deterministic behavior.

Although not at the core of the issue, this example shows how using * rather than an explicit enumeration of desired columns is often a bad idea.

Excerpt from MySQL 5.0 documentation:

When using this feature, all rows in each group should have the same values
for the columns that are omitted from the GROUP BY part. The server is free
to return any value from the group, so the results are indeterminate unless
all values are the same. 

Why does MySQL allow group by queries WITHOUT aggregate functions?

I believe that it was to handle the case where grouping by one field would imply other fields are also being grouped:

SELECT user.id, user.name, COUNT(post.*) AS posts 
FROM user 
  LEFT OUTER JOIN post ON post.owner_id=user.id 
GROUP BY user.id

In this case the user.name will always be unique per user.id, so there is convenience in not requiring the user.name in the GROUP BY clause (although, as you say, there is definite scope for problems)

can more than one aggregation functions be used in `select` in case of `group by`?

You can't nest aggregations in the select list.

If you want to get the percentage of the orders that need to be delivered at the same day as the order per customer then do this:

select customer_id, 
  100.0 * avg(customer_pref_delivery_date = order_date) immediate_percentage
from Delivery
group by customer_id;

See the demo.

Results:

| customer_id | immediate_percentage |
| ----------- | -------------------- |
| 1           | 0                    |
| 2           | 50                   |
| 3           | 50                   |
| 4           | 100                  |

Grouping by multiple columns in MYSQL across multiple tables (INNER JOIN)

I found the answer myself:

SELECT  o.orderid, s.suppliername, COUNT(p.productname) AS numberOfProducts
FROM Orders o
JOIN OrderDetails od
ON o.orderid = od.orderid
JOIN Products p
ON p.productid = od.productid
JOIN Suppliers s 
ON s.supplierid = p.supplierid
GROUP BY o.orderid, s.suppliername
HAVING o.orderid = 10300;

The mainissue was that
ON o.shipperid = s.supplierid had to be
ON s.supplierid = p.supplierid

friendly users on SO helped me out on that :)

---

Related Topics