Expressions GroupBy

GroupBy

Group collections by one or more properties and perform aggregations on each group.

GroupBy Syntax

Single Key GroupBy

Orders:groupBy(Status)

Multiple Keys GroupBy

Orders:groupBy(Year, Month)
Orders:groupBy(Year, Quarter, Region)

GroupBy with Projection

After groupBy, use projection to access key properties and group elements via the Items keyword:

Orders:groupBy(Status).{Status, :count as Count}
 
Orders:groupBy(Status).{ Status, :count as Count, :sum(Total) as Revenue }

Projection Context

Expression	Description
Status	Group key value
Year, Month	Key properties (multi-key)
_	GroupBy does not have an Items. Just need to use method or projection
:count	Number of items in group
:sum(Total)	Sum of property in group
:avg(Price)	Average of property in group
:min(Date)	Minimum value in group
:max(Date)	Maximum value in group

Complex GroupBy Examples

# Group by status with multiple aggregations
Orders:groupBy(Status).{
    Status,
    :count as OrderCount,
    :sum(Total) as TotalRevenue,
    :avg(Total) as AvgOrderValue,
    {Id, Name, UserAddress} as Orders
}
 
# Multi-key grouping (Year, Quarter)
Sales:groupBy(Year, Quarter).{
    Year,
    Quarter,
    :count as SalesCount,
    :sum(Amount) as TotalSales,
    :avg(Amount) as AvgSale
}
 
# Customer grouping with date aggregations
Orders:groupBy(CustomerId).{
    CustomerId,
    :count as OrderCount,
    :sum(Total) as TotalSpent,
    :min(OrderDate) as FirstOrder,
    :max(OrderDate) as LastOrder,
    :any(Status = 'Overdue') as HasOverdue
}

Aliases in GroupBy

Orders:groupBy(Status).{Status as OrderStatus, :count as Count}
 
Orders:groupBy(Year, Month).{Year as Yr, Month as Mo, :sum(Total) as Revenue}

Usage Example

// DTOs for grouped results
public class OrdersByStatusDto
{
    public string Status { get; set; }
    public int OrderCount { get; set; }
    public decimal TotalRevenue { get; set; }
    public decimal AvgOrderValue { get; set; }
}
 
public class MonthlySalesDto
{
    public int Year { get; set; }
    public int Month { get; set; }
    public int SalesCount { get; set; }
    public decimal TotalSales { get; set; }
}
 
public class SalesReportResponse
{
    public string CompanyId { get; set; }
    public List<OrdersByStatusDto> OrdersByStatus { get; set; }
    public List<MonthlySalesDto> MonthlySales { get; set; }
}
 
public class SalesReportResponseProfile : ProfileOf<SalesReportResponse>
{
    protected override void Configure() =>
        UseDistributedKey<CompanyDistributedKey>()
            .Of(x => x.CompanyId)
            // Group orders by status
            .For(x => x.OrdersByStatus, @"
                Orders:groupBy(Status).{
                    Status,
                    :count as OrderCount,
                    :sum(Total) as TotalRevenue,
                    :avg(Total) as AvgOrderValue
                }")
            // Group by year and month
            .For(x => x.MonthlySales, @"
                Sales:groupBy(CreatedAt:year, CreatedAt:month).{
                    CreatedAt:year as Year,
                    CreatedAt:month as Month,
                    :count as SalesCount,
                    :sum(Amount) as TotalSales
                }");
}

Advanced: Yearly Performance Report

Orders:where(Year = 2024, Status != 'Cancelled'):groupBy(Month).{
    Month,
    :count as OrderCount,
    :sum(Total) as Revenue,
    :avg(Total) as AvgOrder
}

GroupBy Limits

• Maximum 5 key properties supported for multi-key groupBy
• Single-key groupBy has no limit

---

Source: https://fxmapper.net/docs/expressions/groupby/ — the website is always the most up to date version.