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;