LINQ Group By With Multiple Aggregates
It appears that LINQ does not support a query that outputs multiple group by aggregate fields. For example, there’s Min() and a Max() functions, so to get both min & max from a list, you seem to need to make 2 LINQ queries:
var min = (from d in data select d).Min();
var max = (from d in data select d).Max();
In SQL, the 2 values would pop out easily from a single query:
Select min(d), max(d) from data
LINQ has all the power of SQL and works similarly, just the syntax is different. To achieve this result we have to use a group by. But we’ll be grouping by nothing, including all items in the set in a single bucket. I usually group by “1”, which makes a single grouping of all the elements (grouping by any single object will do the same):
var minMax = from d in values
group d by 1 into g
select new { Min = g.Min(x => x),
Max = g.Max(x => x) };
minMax.First() now contains the min and max for the set of values.
7 Comments »
Leave a Reply
@Lab49 Now- SurefireBooterForkException – Why? May 29, 2012 Matt Davey
- Retail Banks: Cost-To-Income Ratio May 29, 2012 Matt Davey
- Confusion over Stories, Epics and Themes May 24, 2012 Matt Davey
- JPM: Risk Model Pain May 22, 2012 Matt Davey
- Deutsche Bank Looking to Lead with Lodestone? May 19, 2012 Matt Davey
- Microsoft Industry Reference Architecture for Banking (MIRA-B) May 16, 2012 Matt Davey
- Java final keyword – View? May 16, 2012 Matt Davey
- The iPad Train May 15, 2012 Matt Davey
- Outdated Core Systems May 11, 2012 Matt Davey
- FX Liquidity, “Follow The Sun”, and Price Engines Thoughts May 10, 2012 Matt Davey
- FX Risk in the Cloud May 10, 2012 Matt Davey
- Success With a Proprietary Platform May 10, 2012 Matt Davey
- Certainty of Clearing May 9, 2012 Matt Davey
- DataCentre Operating Systems May 9, 2012 Matt Davey
- Cool Vendor 2012 – Lightstreamer April 26, 2012 Matt Davey
- Architecting for Failure April 25, 2012 Matt Davey
- Sinclair ZX Spectrum – 30 Years Old April 23, 2012 Matt Davey
- BAML’s Mercury FX Options trading platform… April 23, 2012 Haidee Clarke
- UBS Quod Studio – Quant on Demand April 22, 2012 Matt Davey
- Lightstreamer 5.0 Embraces JavaScript April 20, 2012 Matt Davey
If you needed to use those values later in the expression, you could use the “let” keyword in there, as well.
In fact, this prints “1 5″ and is I think a bit more readable:
var nums = new[] { 1, 2, 3, 4, 5 };
var minMax = (from num in nums
let max = nums.Max()
let min = nums.Min()
select new { Maximum = max, Minimum = min }).First();
Console.WriteLine(minMax.Minimum.ToString() + ” ” + minMax.Maximum.ToString());
Hey wait a minute.
Isn’t this the same as:
var x = new { Maximum = nums.Max(), Minimum = nums.Min()};
I don’t know about my query or the optimizer, but this is still 2 linq queries.
If you had to do type conversion or something more complex, would it still be so simple?
Pretty much so…. His version can be rewritten as:
var nums = new[] { 1, 2, 3, 4, 5 };
var one = new[] {1};
var minMax = (from x in one
let max = nums.Max()
let min = nums.Min()
select new { Maximum = max, Minimum = min }).First();
Actually, his is way worse, since those two LINQ queries are in the middle of a pointless loop. i.e., We have an O(2N) solution; we’re looking for an O(N) solution, and he gives us an O(2N^2) algorithm……
Wow, I wasn’t aware of the let keyword for LINQ.
Nice example.
I’ve started to get comfortable with the group by, but I could get used to let.
Thanks.
Well, here it is…. The public API is pretty, even if the implementation is rather last century….
var nums = new[] { 1, 2, 3, 4, 5 };
Console.WriteLine(nums.MinMax());
class MinMax
{
public int Min {get; set;}
public int Max {get; set;}
public override string ToString()
{
return string.Format(“Min:{0}, Max:{1}”, Min, Max);
}
}
static class Util
{
public static MinMax MinMax(this IEnumerable nums)
{
var mm = new MinMax {Min = Int32.MaxValue, Max = Int32.MinValue};
foreach(var n in nums)
{
mm.Min = Math.Min(mm.Min, n);
mm.Max = Math.Max(mm.Max, n);
}
return mm;
}
}
I’m still think about a generic version……
I’m working on a new post on this topic, basically, I think this is the best you can do here:
var x = new { Maximum = nums.Max(), Minimum = nums.Min()};
Getting pretty busy, so it may be a while for me to explain why.