Edit

Share via

GROUPPARTITION (Entity SQL)

  • Article

Returns a collection of argument values that are projected off the current group partition to which the aggregate is related. The GroupPartition aggregate is a group-based aggregate and has no collection-based form.

Syntax

GROUPPARTITION( [ALL|DISTINCT] expression )

Arguments

expression Any Entity SQL expression.

Remarks

The following query produces a list of products and a collection of order line quantities per each product:

SELECT p, GroupPartition(ol.Quantity) FROM LOB.OrderLines AS ol
  GROUP BY ol.Product AS p

The following two queries are semantically equal:

SELECT p, Sum(GroupPartition(ol.Quantity)) FROM LOB.OrderLines AS ol
  GROUP BY ol.Product AS p
SELECT p, Sum(ol.Quantity) FROM LOB.OrderLines AS ol
  group by ol.Product as p

The GROUPPARTITION operator can be used in conjunction with user-defined aggregate functions.

GROUPPARTITION is a special aggregate operator that holds a reference to the grouped input set. This reference can be used anywhere in the query where GROUP BY is in scope. For example:

SELECT p, GroupPartition(ol.Quantity) FROM LOB.OrderLines AS ol GROUP BY ol.Product AS p

With a regular GROUP BY, the results of the grouping are hidden. You can only use the results in an aggregate function. In order to see the results of the grouping, you have to correlate the results of the grouping and the input set by using a subquery. The following two queries are equivalent:

SELECT p, (SELECT q FROM GroupPartition(ol.Quantity) AS q) FROM LOB.OrderLines AS ol GROUP BY ol.Product AS p
SELECT p, (SELECT ol.Quantity AS q FROM LOB.OrderLines AS ol2 WHERE ol2.Product = p) FROM LOB.OrderLines AS ol GROUP BY ol.Product AS p

As seen from the example, the GROUPPARTITION aggregate operator makes it easier to get a reference to the input set after the grouping.

The GROUPPARTITION operator can specify any Entity SQL expression in the operator input when you use the expression parameter.

For instance all of the following input expressions to the group partition are valid:

SELECT groupkey, GroupPartition(b) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition(1) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition(a + b) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition({a + b}) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition({42}) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition(b > a) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey

Example

The following example shows how to use the GROUPPARTITION clause with the GROUP BY clause. The GROUP BY clause groups SalesOrderHeader entities by their Contact. The GROUPPARTITION clause then projects the TotalDue property for each group, resulting in a collection of decimals.

USING Microsoft.Samples.Entity
Function MyAvg(dues Collection(Decimal)) AS
(
    Avg(SELECT value due FROM dues AS due WHERE due > @price)
)
SELECT TOP(10) contactID, MyAvg(GroupPartition(order.TotalDue)) 
FROM AdventureWorksEntities.SalesOrderHeaders AS order 
GROUP BY order.Contact.ContactID AS contactID;