patternMinor
GROUP BY on a dependent field
Viewed 0 times
fielddependentgroup
Problem
Let's say I have a SELECT statement as follows:
Now let's say I want to return a second field,
Option 1:
Option 2:
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?
SELECT ..., field1
FROM ...
GROUP BY field1Now 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, field2Option 2:
SELECT ..., field1, MIN(field2) /* or some other, arbitrary aggregate function */
FROM ...
GROUP BY field1Again, 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:
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.CustomerIDCode 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.CustomerIDContext
StackExchange Database Administrators Q#27087, answer score: 5
Revisions (0)
No revisions yet.