GROUP BY

Specifies groups into which objects returned by a query (SELECT) expression are to be placed.

Syntax

[ 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.
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 name name to the product as part of the grouping expression, and then references that name in the SELECT list. It also specifies the aggregate sum in the SELECT list that internally references the price and quantity of the order line.
SELECT FROM Persons as P
GROUP BY Q + P   -- GOOD
GROUP BY Q   -- BAD
GROUP BY 1   -- BAD, a constant is not allowed

Examples
var esql = "select SID from Products as p group by p.SupplierID as SID";
var q = db.CreateQuery<IDataRecord>(esql);
var esql = "select SupplierID from Products as p group by p.SupplierID";
var q = db.CreateQuery<IDataRecord>(esql);
var esql = @"select ProductId, sum(o.Quantity) as SumQuantity  
             from OrderDetails as o 
             group by o.ProductId";
var q = db.CreateQuery(esql);
var esql = @"select CategoryID, SupplierID, Count()  
            from Products as p 
            group by p.CategoryID, p.SupplierID";
var db.CreateQuery<IDataRecord>(esql).Execute();
select OrderID
from ( select o, d 
       from Orders as o 
            inner join OrderDetails as d on o.OrderID = d.OrderID ) as od
group by od.o.OrderID

Last edited Mar 9, 2013 at 6:56 AM by ansiboy, version 2

Comments

No comments yet.