2 people like it.

Basic F# LINQ GroupBy example and multiple aggregates

Converting F# LINQ queries to SQL: - Basic example of simple group-by - Also if you want to have multiple aggregates (SUM, COUNT, MAX, MIN, AVG, ...) in the same SQL-clause, that is done with groupBy over a constant value.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 

// Multiple aggregates has to be done via groupBy constant even when SQL doesn't have it:
// SELECT COUNT(1), SUM(UnitPrice) FROM Products
let countAndSum = 
	query {
		for p in context.Main.Products do
		groupBy 1 into g
		select (g.Count(), g.Sum(fun p -> p.UnitPrice))
	} |> Seq.head

// Basic Group by:
// SELECT ShipCity, SUM(Freight) FROM Orders GROUP BY ShipCity
let freightsByCity =
    query {
        for o in context.Main.Orders do
        groupBy o.ShipCity into cites
        select (cites.Key, cites.Sum(fun order -> order.Freight))
    } |> Seq.toList
val countAndSum : obj * obj

Full name: Script.countAndSum
val query : Linq.QueryBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.query
val p : obj
custom operation: groupBy ('Key)

Calls Linq.QueryBuilder.GroupBy
val g : System.Linq.IGrouping<int,obj>
custom operation: select ('Result)

Calls Linq.QueryBuilder.Select
module Seq

from Microsoft.FSharp.Collections
val head : source:seq<'T> -> 'T

Full name: Microsoft.FSharp.Collections.Seq.head
val freightsByCity : (obj * obj) list

Full name: Script.freightsByCity
val o : obj
val cites : System.Linq.IGrouping<obj,obj>
property System.Linq.IGrouping.Key: obj
val toList : source:seq<'T> -> 'T list

Full name: Microsoft.FSharp.Collections.Seq.toList
Raw view Test code New version

More information

Link:http://fssnip.net/7X3
Posted:5 years ago
Author:Tuomas Hietanen
Tags: aggregate , average , count , groupby , linq , query , sql , sumby

This web site is created using F# and Suave web server. It is hosted on Azure and the source code is on GitHub. Contributions are welcome!

The first version of fssnip.net has been created by @tomaspetricek back in 2010. This web site is a new, open-source and contribution-friendly version.