Introduction
LINQ's GroupBy, Sum, and Count methods are the C# equivalents of SQL's GROUP BY with aggregate functions. GroupBy partitions a collection by a key, then Sum and Count compute aggregates within each group. These are essential for reporting, analytics, and data transformation in .NET applications.
Basic GroupBy with Count
1var orders = new List<Order>
2{
3 new Order { Product = "Widget", Amount = 25.00m, Region = "East" },
4 new Order { Product = "Gadget", Amount = 50.00m, Region = "West" },
5 new Order { Product = "Widget", Amount = 30.00m, Region = "East" },
6 new Order { Product = "Gadget", Amount = 45.00m, Region = "East" },
7 new Order { Product = "Widget", Amount = 20.00m, Region = "West" },
8};
9
10// Count orders per product
11var countByProduct = orders
12 .GroupBy(o => o.Product)
13 .Select(g => new { Product = g.Key, Count = g.Count() });
14
15foreach (var item in countByProduct)
16 Console.WriteLine($"{item.Product}: {item.Count}");
17// Widget: 3
18// Gadget: 2
GroupBy with Sum
1// Total amount per product
2var sumByProduct = orders
3 .GroupBy(o => o.Product)
4 .Select(g => new
5 {
6 Product = g.Key,
7 TotalAmount = g.Sum(o => o.Amount),
8 Count = g.Count()
9 });
10
11foreach (var item in sumByProduct)
12 Console.WriteLine($"{item.Product}: Total={item.TotalAmount:C}, Count={item.Count}");
13// Widget: Total=$75.00, Count=3
14// Gadget: Total=$95.00, Count=2
Query Syntax (SQL-like)
1var result = from o in orders
2 group o by o.Product into g
3 select new
4 {
5 Product = g.Key,
6 TotalAmount = g.Sum(o => o.Amount),
7 Count = g.Count(),
8 Average = g.Average(o => o.Amount)
9 };
Query syntax is often more readable for developers coming from SQL.
GroupBy Multiple Keys
Group by a composite key using an anonymous type:
1var byProductAndRegion = orders
2 .GroupBy(o => new { o.Product, o.Region })
3 .Select(g => new
4 {
5 g.Key.Product,
6 g.Key.Region,
7 TotalAmount = g.Sum(o => o.Amount),
8 Count = g.Count()
9 });
10
11foreach (var item in byProductAndRegion)
12 Console.WriteLine($"{item.Product} ({item.Region}): {item.TotalAmount:C} ({item.Count} orders)");
13// Widget (East): $55.00 (2 orders)
14// Gadget (West): $50.00 (1 orders)
15// Gadget (East): $45.00 (1 orders)
16// Widget (West): $20.00 (1 orders)
Multiple Aggregations
Compute several aggregates in one pass:
1var stats = orders
2 .GroupBy(o => o.Product)
3 .Select(g => new
4 {
5 Product = g.Key,
6 Count = g.Count(),
7 Total = g.Sum(o => o.Amount),
8 Average = g.Average(o => o.Amount),
9 Min = g.Min(o => o.Amount),
10 Max = g.Max(o => o.Amount)
11 })
12 .OrderByDescending(x => x.Total);
13
14foreach (var s in stats)
15 Console.WriteLine($"{s.Product}: Count={s.Count}, Total={s.Total:C}, " +
16 $"Avg={s.Average:C}, Min={s.Min:C}, Max={s.Max:C}");
Conditional Count
Count only items meeting a condition within each group:
1var result = orders
2 .GroupBy(o => o.Region)
3 .Select(g => new
4 {
5 Region = g.Key,
6 TotalOrders = g.Count(),
7 HighValueOrders = g.Count(o => o.Amount >= 40),
8 LowValueOrders = g.Count(o => o.Amount < 40),
9 HighValueSum = g.Where(o => o.Amount >= 40).Sum(o => o.Amount)
10 });
GroupBy with Into (Continuation)
Chain additional operations after grouping using query syntax:
1var topProducts = from o in orders
2 group o by o.Product into g
3 let total = g.Sum(o => o.Amount)
4 where total > 50
5 orderby total descending
6 select new { Product = g.Key, Total = total };
Real-World Example: Sales Report
1var salesReport = transactions
2 .Where(t => t.Date.Year == 2024)
3 .GroupBy(t => new { t.Date.Month, t.Category })
4 .Select(g => new
5 {
6 Month = g.Key.Month,
7 Category = g.Key.Category,
8 Revenue = g.Sum(t => t.Amount),
9 TransactionCount = g.Count(),
10 AverageOrderValue = g.Average(t => t.Amount),
11 UniqueCustomers = g.Select(t => t.CustomerId).Distinct().Count()
12 })
13 .OrderBy(x => x.Month)
14 .ThenByDescending(x => x.Revenue);
ToDictionary and ToLookup
Convert grouped results to dictionaries for fast lookup:
1// Dictionary: one value per key
2var totalByProduct = orders
3 .GroupBy(o => o.Product)
4 .ToDictionary(g => g.Key, g => g.Sum(o => o.Amount));
5
6Console.WriteLine(totalByProduct["Widget"]); // 75.00
7
8// Lookup: multiple values per key (like a multimap)
9var ordersByRegion = orders.ToLookup(o => o.Region);
10
11foreach (var order in ordersByRegion["East"])
12 Console.WriteLine($" {order.Product}: {order.Amount:C}");
For very large datasets, manual dictionary accumulation can be faster:
1// LINQ GroupBy (clean but allocates groupings)
2var result = items.GroupBy(x => x.Key).Select(g => new { g.Key, Sum = g.Sum(x => x.Value) });
3
4// Manual dictionary (less allocation)
5var dict = new Dictionary<string, decimal>();
6foreach (var item in items)
7{
8 if (dict.ContainsKey(item.Key))
9 dict[item.Key] += item.Value;
10 else
11 dict[item.Key] = item.Value;
12}
For typical business applications, LINQ's readability outweighs the performance difference.
Common Pitfalls
Deferred execution: GroupBy returns an IEnumerable that is evaluated lazily. If the source collection changes between creating and iterating the query, results may be unexpected. Materialize with .ToList() if needed.
Null keys: GroupBy supports null keys — all items with null keys are grouped together. This may or may not be the desired behavior.
Anonymous type equality: When grouping by multiple keys with new { A, B }, anonymous types use structural equality. This works correctly, but you cannot return anonymous types from methods — use named types or records instead.
Count() vs Count property: g.Count() is a LINQ method that iterates the group. For List<T>, use .Count property (O(1)) instead of .Count() method (O(n)). Within GroupBy, you must use the method.
Large group counts: GroupBy loads all groups into memory. For millions of rows, consider streaming approaches or database-side grouping with Entity Framework.
Summary
Use GroupBy(x => x.Key) to partition data, then .Select() to compute aggregates
Chain .Sum(), .Count(), .Average(), .Min(), .Max() on each group
Group by multiple keys with new { Key1, Key2 } anonymous types
Use query syntax (group by ... into g) for SQL-like readability
Convert to Dictionary or Lookup for fast key-based access to grouped results
