Method GroupBy

Namespace

YndigoBlue.Velocity.Model

Assembly

YndigoBlue.Velocity.dll

GroupBy(IGroupItem)

Adds a GROUP BY clause. Fluent API method.

public Query GroupBy(IGroupItem groupItem)

Parameters

groupItem IGroupItem

The column to group by.

Returns

Query

This query instance for method chaining.

GroupBy(IEnumerable<IGroupItem>)

Adds multiple GROUP BY clauses. Fluent API method.

public Query GroupBy(IEnumerable<IGroupItem> groupItems)

Parameters

groupItems IEnumerable<IGroupItem>

The columns to group by.

Returns

Query

This query instance for method chaining.

GroupBy(IGroupItem, Query)

Adds a GROUP BY clause on a column of a derived table. Fluent API method.

public Query GroupBy(IGroupItem groupItem, Query subQuery)

Parameters

groupItem IGroupItem

The item naming the column to group by. Only its name is used.

subQuery Query

The subquery in the FROM clause that produces the column.

Returns

Query

This query instance for method chaining.

Examples

Grouping by a classification computed in a subquery:

var band = new Case("band")
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 100), new Literal<string>("Bulk"))
    .Else(new Literal<string>("Small"));

var inner = new Query().Select([ orders["order_id"], band ]).From(orders);

var outer = new Query()
    .Select(band, "band", inner)
    .Select(new Expression("band_count", new Count()))
    .From(inner, "x")
    .GroupBy(band, inner);

// SQL: SELECT "x"."band" AS "band", COUNT(*) AS "band_count"
//      FROM ( SELECT order_id, CASE WHEN quantity > @p0 THEN @p1 ELSE @p2 END AS "band" FROM orders ) AS "x"
//      GROUP BY "x"."band"

Remarks

Without the subquery, a group item is resolved against the tables in the FROM clause, so it can only name a column of a real table. This overload resolves it against a derived table instead, producing a qualified reference to the subquery's output column — the same mechanism Select(ISelectItem, string, Query) and OrderBy(OrderClause, Query) already use.

Only Name is taken from groupItem. Passing the Case that produced the column therefore groups by its alias rather than repeating the whole expression, which is what makes it work on every supported datasource: DB2 and Teradata reject a GROUP BY expression containing parameter markers, and grouping a plain column of a derived table has none.

GroupBy(IEnumerable<IGroupItem>, Query)

Adds multiple GROUP BY clauses on columns of a derived table. Fluent API method.

public Query GroupBy(IEnumerable<IGroupItem> groupItems, Query subQuery)

Parameters

groupItems IEnumerable<IGroupItem>

The items naming the columns to group by. Only their names are used.

subQuery Query

The subquery in the FROM clause that produces the columns.

Returns

Query

This query instance for method chaining.

Remarks

See GroupBy(IGroupItem, Query) for details and an example.