Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Postgres allows you to project it anyway if it is functionally dependant on the group by columns. as per the standard according to here –  Martin Smith Oct 17 '12 at 13:56
add comment

1 Answer

up vote 5 down vote accepted

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
share|improve this answer
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. –  AlexKuznetsov Oct 17 '12 at 13:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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