Grouping

Grouping (the GROUP BY clause) and aggregating results is done with the GroupBy and Having methods. The following example generates an expression tree for counting the number of blog posts per author:

var author = new SqlColumn("CreatedBy", "Author");
var sql = Sql
    .Select(author, SqlAggregate.Count("Id", "Count"))
    .From("Post")
    .GroupBy("CreatedBy")
    .ToSql();

Yielding the following SQL statement:

SELECT [CreatedBy] AS [Author], COUNT([Id]) AS [Count] FROM [Post] GROUP BY ([CreatedBy])

You can also use the Having method to add a HAVING clause to filter your grouping. For example, the following code produces a query for counting the total number of people per age from the Profile table, only when they are 18 years or older:

var profiles = new SqlTable("Profile", "p");
var sql = Sql
    .Select(profiles + "Age", SqlAggregate.Count(profiles + "Id", "Count"))
    .From(profiles)
    .GroupBy(profiles + "Age")
    .Having(SqlExpression.GreaterThanOrEqual(profiles + "Age", 18))
    .ToSql();

Which results in the following query:

SELECT [p].[Age], COUNT([p].[Id]) AS [Count] FROM [Profile] [p] 
GROUP BY ([p].[Age]) HAVING [p].[Age] >= 18