5

Let's say I have a SELECT statement as follows:

SELECT ..., field1
  FROM ...
 GROUP BY field1

Now let's say I want to return a second field, field2. As it happens, field2 is functionally dependent on field1 (for example, field1 and field2 could be from the same table and field1 is the primary key of that table). I now have two equivalent ways to add field2 to the query:

Option 1:

SELECT ..., field1, field2
  FROM ...
 GROUP BY field1, field2

Option 2:

SELECT ..., field1, MIN(field2)  /* or some other, arbitrary aggregate function */
  FROM ...
 GROUP BY field1

Again, since field2 functionally depends on field1, both queries should return the same result set.

Are there any good reasons to prefer one option over the other?

5

The answer can be platform-specific. Although the results are the same, your performance might be very different.

I would suggest another approach, because it seems to clearly document the following intent: calculate some aggregates, then decorate them with another field(s). Here is an example:

WITH CustomerTotals AS(
SELECT CustomerID,
SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
)
SELECT Customers.Name, CustomerTotals.TotalAmount
FROM  CustomerTotals JOIN Customers 
  ON CustomerTotals.CustomerID = Customers.CustomerID
  • 4
    I think you've missed a GROUP BY from your CTE. – dezso Oct 17 '12 at 13:14
  • @dezso I did. Thank you for noticing, I fixed it. – A-K Oct 17 '12 at 13:46

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.