Alex Rivera | Logout

Is the GROUP BY clause in SQL redundant?

Asked 2010-12-22T01:12:00.147
13

Whenever we use an aggregate function in SQL (MIN, MAX, AVG etc), we must always GROUP BY all non-aggregated columns, for instance:

SELECT storeid, storename, SUM(revenue), COUNT(*)
FROM Sales 
GROUP BY storeid, storename

It becomes even more intrusive when we use a function or other calculation in our SELECT statement, as this must also be copied to the GROUP BY clause.

SELECT (2 * (x + y)) / z + 1, MyFunction(x, y), SUM(z)
FROM AnotherTable
GROUP BY (2 * (x + y)) / z + 1, MyFunction(x, y)

If we ever change the SELECT statement, we must remember to make the same change to our GROUP BY clause.

So is the GROUP BY clause is redundant?

  • If this is indeed the case, then why is there a GROUP BY clause in SQL at all?
  • If this is not the case, then what extra functionality does GROUP BY give us?
sql group-by
Edit
Report

1 Answer

5

I may agree with what you're saying, but it is not redundant in all cases.

Consider this: 

SELECT FirstName 
       + ' (' + REPLACE(Address1, ',', ' ') + ' '
       + REPLACE(Address2, ',', ' ') + ', '
       + UPPER(State) + ' '
       + 'USA)',
       COUNT(*)
FROM Profiles
GROUP BY FirstName, Address1, Address2, State

In this case I just want the number of same-first-name, same-address profiles.
As you can see, I didn't have to repeat the "complex" operations of the SELECT in the GROUP BY statement.

I think to allow this "sometimes like this, sometimes like that", you are taxed with having to do repetitions most of the time.

answered 2010-12-22T01:20:29.210

Your Answer