GROUP BY (Entity SQL)
Specifies groups into which objects returned by a query (SELECT) expression are to be placed.
[ GROUP BY aliasedExpression [ ,...n ] ]
Arguments
- aliasedExpression
Any valid query expression on which grouping is performed. expression can be a property or a non-aggregate expression that references a property returned by the FROM clause. Each expression in a GROUP BY clause must evaluate to a type that can be compared for equality. These types are generally scalar primitives such as numbers, strings, and dates. You cannot group by a collection.
Remarks
If aggregate functions are included in the SELECT clause <select list>, GROUP BY calculates a summary value for each group. When GROUP BY is specified, either each property name in any nonaggregate expression in the select list should be included in the GROUP BY list, or the GROUP BY expression must exactly match the select list expression.
Note
If the ORDER BY clause is not specified, groups returned by the GROUP BY clause are not in any particular order. To specify a particular ordering of the data, we recommend that you always use the ORDER BY clause.
When a GROUP BY clause is specified, either explicitly or implicitly (for example, by a HAVING clause in the query), the current scope is hidden, and a new scope is introduced.
The SELECT clause, the HAVING clause, and the ORDER BY clause will no longer be able to refer to element names specified in the FROM clause. You can only refer to the grouping expressions themselves. To do this, you can assign new names (aliases) to each grouping expression. If no alias is specified for a grouping expression, Entity SQL tries to generate one by using the alias generation rules, as illustrated in the following example.
SELECT g1, g2, ...gn FROM c as c1
GROUP BY e1 as g1, e2 as g2, ...en as gn
Expressions in the GROUP BY clause cannot refer to names defined earlier in the same GROUP BY clause.
In addition to grouping names, you can also specify aggregates in the SELECT clause, HAVING clause, and the ORDER BY clause. An aggregate contains an expression that is evaluated for each element of the group. The aggregate operator reduces the values of all these expressions (usually, but not always, into a single value). The aggregate expression can make references to the original element names visible in the parent scope, or to any of the new names introduced by the GROUP BY clause itself. Although the aggregates appear in the SELECT clause, HAVING clause, and ORDER BY clause, they are actually evaluated under the same scope as the grouping expressions, as illustrated in the following example.
SELECT name, sum(o.Price * o.Quantity) as total
FROM orderLines as o
GROUP BY o.Product as name
This query uses the GROUP BY clause to produce a report of the cost of all products ordered, broken down by product. It gives the namenameto the product as part of the grouping expression, and then references that name in the SELECT list. It also specifies the aggregatesumin the SELECT list that internally references the price and quantity of the order line.
Each GROUP By key expression must have at least one reference to the input scope:
SELECT FROM Persons as P
GROUP BY Q + P -- GOOD
GROUP BY Q -- BAD
GROUP BY 1 -- BAD, a constant is not allowed
For an example of using GROUP BY, see HAVING (Entity SQL).
Example
The following Entity SQL query uses the GROUP BY operator to specify groups into which objects are returned by a query. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:
Follow the procedure in How to: Execute a Query that Returns PrimitiveType Results (EntityClient).
Pass the following query as an argument to the
ExecutePrimitiveTypeQuerymethod:
SELECT VALUE name FROM AdventureWorksEntities.Product
as P GROUP BY P.Name HAVING MAX(P.ListPrice) > 5
The output is shown below:
Value: LL Mountain Seat Assembly
Value: ML Mountain Seat Assembly
Value: HL Mountain Seat Assembly
Value: LL Road Seat Assembly
Value: ML Road Seat Assembly