GROUP BY Clause

Query results can be grouped using GroupBy(). Remember to aggregate any columns in the select list that arent’ in the grouping expression list.

var selectStatement = Select
    .All("ID".Count(), "Name")
    .From("Customer")
    .GroupBy("Name");

SELECT COUNT([ID]), [Name]
FROM [Customer]
GROUP BY [Name];

GroupBy() is available everywhere you’d expect. For example, it can be chained off the From() method or off the Where() method.

var selectStatement = Select
    .All("ID".Count(), "Name")
    .From("Customer")
    .Where("LastUpdated".IsGreaterThan(new DateTime(2024, 9, 1)))
    .GroupBy("Name");

SELECT COUNT([ID]), [Name]
FROM [Customer]
WHERE [LastUpdated] > '2024-09-01 00:00:00.000'
GROUP BY [Name];

There are rules about what can appear in GROUP BY’s expression list and they’re not enforced by the Fluent SQL syntax or at compile-time, so it’s possible to construct queries that will fail at execution time.

ROLLUP and CUBE

In addition to the GroupBy() method you can also use the ROLLUP and CUBE options with the GroupByRollup() and GroupByCube() methods.

var selectStatement = Select
    .All("ID".Count(), "Country", "Region")
    .From("Customer")
    .GroupByRollup("Country", "Region");

SELECT COUNT([ID]), [Country], [Region]
FROM [Customer]
GROUP BY ROLLUP ( [Country], [Region] );

Grouping Sets

Grouping sets have not yet been implemented in Fluent SQL. In the meantime, you can work around this gap with a UNION ALL of the individual sets. (See T-SQL | GROUP BY Clause.)

HAVING

Grouped rows can be filtered with Having().

var selectStatement = Select
    .All("ID".Count(), "Name")
    .From("Customer")
    .GroupBy("Name")
    .Having("ID".Count().IsGreaterThan(1));

SELECT  COUNT([ID]), [Name]
FROM [Customer]
GROUP BY [Name]
HAVING COUNT([ID]) > 1;