CODEWITHSUNDEEP
c#linq
Nate
Nate

Reputation: 2326

Linq average count by day of week

I'm trying to get the total average count of row instances by day of week. So over the past year, I'm trying to get the average amount of rides that happened on a monday/tuesday/wed/ect.

Here's what I have so far. That gives me the total count per day of week, but not the average count.

UnitOfWork.Query<WorkoutRecord>()
   .Where(x => x.WorkoutDate > baseDate)
   .GroupBy(x => SqlFunctions.DatePart("weekday", x.UploadDate))
   .Select(x => new AdminDashboardWorkoutsGroupedDay()
   {
       DayOfWeek = (DayOfWeek)x.Key,
       WorkoutCount = x.Count()
   }).ToList();

Upvotes: 0

Views: 848

Answers (3)

Nate
Nate

Reputation: 2326

Here's what worked. Thanks @Gert Arnold, you got me really close. I had to group by day and count all the workouts, then take the average of that grouping by weekday.

UnitOfWork.Query<WorkoutRecord>()
    .Where(x => x.WorkoutDate > baseDate && x.TotalTicks > 600)
    .GroupBy(x => EntityFunctions.TruncateTime(x.UploadDate))
    .Select(x => new 
    {
        Date = x.Key ?? DateTime.UtcNow,
        TheCount = x.Count()
    })
    .GroupBy(x=> SqlFunctions.DatePart("weekday", x.Date))
    .Select (x => new AdminDashboardWorkoutsGroupedDay()
    {
        WorkoutCount = (x.Average(y=>y.TheCount)),
        DayOfWeek = (DayOfWeek)x.Key
    })
    .ToList()

Upvotes: 1

Gert Arnold
Gert Arnold

Reputation: 109251

If I understand you well, in the end you're trying to get one number, the average count per weekday. This requires a second grouping that reduces the data to one group:

UnitOfWork.Query<WorkoutRecord>()
   .Where(x => x.WorkoutDate > baseDate)
   .GroupBy(x => SqlFunctions.DatePart("weekday", x.UploadDate))
   .Select(x => new AdminDashboardWorkoutsGroupedDay()
   {
       DayOfWeek = (DayOfWeek)x.Key,
       WorkoutCount = x.Count()
   })
   .GroupBy(g => 0) // or g => "x", or whatever
   .Select (g1 => (decimal)g1.Sum(x => x.WorkoutCount) / g1.Count())
   .ToList();

Upvotes: 2

Eren Ers&#246;nmez
Eren Ers&#246;nmez

Reputation: 39095

It's not clear what property you want the average of, but assuming you have a property like WorkoutRecord.Duration, you should be able to get the average like:

UnitOfWork.Query<WorkoutRecord>()
    .Where(x => x.WorkoutDate > baseDate)
    .GroupBy(x => SqlFunctions.DatePart("weekday", x.UploadDate))
    .Select(x => new AdminDashboardWorkoutsGroupedDay()
    {
        DayOfWeek = (DayOfWeek)x.Key,
        AverageDuration = x.Average(w => w.Duration)
    })
    .ToList();

Upvotes: -1

Related Questions