0

I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and employees:

OrderID     CustomerID  EmployeeID  OrderDate   ShipperID
10248   90  5   1996-07-04  3
10249   81  6   1996-07-05  1
10250   34  4   1996-07-08  2

ShipperID   ShipperName     Phone
1   Speedy Express  (503) 555-9831
2   United Package  (503) 555-3199
3   Federal Shipping    (503) 555-9931


EmployeeID  LastName    FirstName   BirthDate   Photo   Notes
1   Davolio     Nancy   1968-12-08  EmpID1.pic  Education includes a BA....
2   Fuller  Andrew  1952-02-19  EmpID2.pic  Andrew received his BTS....
3   Leverling   Janet   1963-08-30  EmpID3.pic  Janet has a BS degree...

We can then determine the number of orders sent by a shipper:

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName; 

Group By helps here because it prevents a duplicate shipper name in the result set. That is, the aggregate function itself without using GROUP BY would return two rows of shipper name if shipper name appears more than once in the shippers table. GROUP BY gives our aggregate without duplicates.

Makes sense. But then I come across this result set from an ORM (ActiveRecord in Rails in this case):

SELECT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700')
GROUP BY users.id

There were no aggregate functions in that sql statement. Shouldn't it be using ORDER BY instead?

  • is id the primary key of the users table?, also, which version of postgresql are you using? – Lamak Aug 7 '14 at 20:12
  • yes id is primary key of users table. The database might be postgresql or mysql. It doesn't specify. I got the example from the book "Rails 3 Way". – JohnMerlino Aug 7 '14 at 20:16
  • 2
    Well, the database engine (and version) that you'll use is rather important to see if that particular way of using GROUP BY is allowed. Basically, MySQL lets you use GROUP BY by only one column even if you are selecting more than that, and the results are unique in those columns and the other columns take one value at random (seldom). Postgresql (don't remember since which version) lets you do that only if the columns that aren't in the GROUP BY are functionally dependant on the columns that are used in the GROUP BY – Lamak Aug 7 '14 at 20:19
  • @Lamak the book is using mysql, not postgresql. Initially I thought it was postgresql. – JohnMerlino Aug 8 '14 at 4:04
  • @lamak In MySQL that is allowed only if the sql_mode 'ONLY_FULL_GROUP_BY' is disabled. When enable, MySQL is even more restrictive than PostgreSQL/SQL standard (which is not a good thing in my opinion). – jynus Aug 8 '14 at 10:46
3

This:

SELECT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700')
GROUP BY users.id

Finds all users who have a timesheet submitted on or before the given date. It's equivalent to:

SELECT DISTINCT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700');

or:

SELECT  users.*
FROM users
WHERE EXISTS (
    SELECT 1
    FROM timesheets 
    WHERE timesheets.user_id = users.id
    AND timesheets.submitted_at <= '2010-07-06 15:27:05.117700'
);

It works because users.id is the primary key, so all other fields of users are functionally dependent on it. PostgreSQL knows that you don't have to use an aggregate to guarantee a single unambiguous result for each field in a row because there can only be one candidate users.name or whatever for any given users.id row.

(Older PostgreSQL versions didn't know how to identify functional dependencies of the primary key and and would throw an ERROR about needing to use an aggregate or include the field in the GROUP BY here).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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