HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

GROUP BY on a dependent field

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
fielddependentgroup

Problem

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?

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#27087, answer score: 5

Revisions (0)

No revisions yet.