Using LINQ to return average of distinct values

I don’t have much experience with LINQ and I’m having difficulties using LINQ to extract the average, mode and median for a column of a ListView based on distinct values in a different column.

For example, consider this data:

ReportID  TAT  Col3  Col4  etc...
--------  ---  ----  ----  -------
80424     9    1     7     ...
80424     9    2     3     ...
80424     9    2     2     ...
80423     5    1     5     ...
80425     4    1     5     ...
80423     7    1     4     ...
82541     7    1     5     ...

To extract the count of distinct reports that have a TAT (turnaround time) greater than 5 days, I use the following:

int countDistinctReports = (from ListViewItem itm in lvList.Items
                            where itm.SubItems[1].Text) > 5
                            select itm.SubItems[0].Text
                           ).Distinct().Count();

The value of countDistinctReports is 3.

To extract the minimum TAT for these reports I use this:

string minVal = (from ListViewItem itm in lvList.Items
             where itm.SubItems[1].Text) > 5
             select itm.SubItems[1].Text
            ).Min();

The value of minVal is 7.

However, I don’t know how to write LINQ expressions to calculate the average, mode and median TAT of distinct reports that have their TAT greater than 5. In the above example, the average should be (9+7+7)/3 = 7.67. The mode should be 7.

Can someone please help? Many thanks.

Answer

Here’s the problem reproduced and solved, only using linq 1-liners:

using System;
using System.Linq;

namespace StackOverflow_StatisticsLinq
{
    class Program
    {
        static void Main(string[] args)
        {
            var reports = new int[][]
            {
                new int[] {80424, 9, 1, 7},
                new int[] {80424, 9, 2, 3},
                new int[] {80424, 9, 2, 2},
                new int[] {80423, 5, 1, 5},
                new int[] {80425, 4, 1, 5},
                new int[] {80423, 7, 1, 4},
                new int[] {80541, 7, 1, 5}
            };

            var reportsTat5Plus = (
                    from int[] r in reports
                    where r[1] > 5
                    orderby r[1] descending
                    select r)
                .GroupBy(r => r[0])
                .Select(r => r.First()); // ensure ids are distinct
            
            var average = reportsTat5Plus.Select(r => r[1]).Average();
            
            var mode = reportsTat5Plus.GroupBy(v => v)
                .OrderByDescending(g => g.Count())
                .First()
                .Select(r => r[1])
                .First();
            
            var median = reportsTat5Plus.Count() % 2 == 1
                ? reportsTat5Plus.Skip(reportsTat5Plus.Count() / 2 + 1).Select(r => r[1]).First()
                : reportsTat5Plus.Skip(reportsTat5Plus.Count() / 2 - 1).Take(2).Select(r => r[1]).Average();

            Console.WriteLine($"Average: {average}");
            Console.WriteLine($"mode: {mode}");
            Console.WriteLine($"median: {median}");
            Console.ReadKey();
        }
    }
}