CODEWITHSUNDEEP
c#linq-to-sql
JCM
JCM

Reputation: 151

Grouping with an average of it using LINQ to SQL

I have a c# program that I am programing where someone inputs production for the whole day and I calculate the Machine Usage (MU) like this:

Date      | Part Number | Mold Num | Machine Num |  MU  
2/12/2016 | 1185-5B8    |  6580    | 12          |  .428            
2/12/2016 | 2249300     |  7797    | 36          |  .271            
2/12/2016 | 146865      |  5096789 | 12          |  .260            
2/16/2016 | 123456      |  7787    | 56          |  .354            
2/16/2016 | 123456      |  787     | 54          |  .45            
2/16/2016 | 123456      |  777     | 56          |  .799            
2/16/2016 | 123456      |  87      | 54          |  .611

How would I group the data that is all in the same table like so:

2/12/2016                   2/16/2016
Machine Num. | MU           Machine Num. | MU
    12       | 34.4%            54       | 53.0%
    36       | 27.1%            56       | 57.6%

The table where everything is at is called [MU Report]

EDIT:

MU = Time Ran / Time available. So what I did was calculate MU for each individual entry to take an average of it later.

Upvotes: 1

Views: 264

Answers (2)

Leandro Soares
Leandro Soares

Reputation: 2972

Working Demo

Class (Sample):

public class MachineData
{
    public DateTime Date { get; set; }

    public string PartNumber { get; set; }

    public int MoldNum { get; set; }

    public int MachineNum { get; set; }

    public decimal MU { get; set; }
}

Query:

var query =
    dbItems
        .GroupBy(x => new { x.Date, x.MachineNum })
        .Select(x => new
        {
            Date = x.Key.Date,
            MachineNum = x.Key.MachineNum,
            AverageMU = x.Average(i => i.MU * 100)
        })
        .ToList();

GroupBy(x => new { x.Date, x.MachineNum })

Here you can group all the items by Date and MachineNum

.Select(x => new
{
      Date = x.Key.Date,
      MachineNum = x.Key.MachineNum,
      AverageMU = x.Average(i => i.MU * 100)
      // Instead of x.Average you may used other functions like: Count, Any etc.
})

The following Select will create an anonymous type which will contain: Date, MachineNum and the average of MU * 100 to get the percentage.

PS: I used lambda instead of pure LINQ because i don't really like to work with LINQ

Upvotes: 1

Zein Makki
Zein Makki

Reputation: 30022

Since the average is based on the day only and the grouping is based on both the Date and the Machine number, you need to-level grouping:

a. Calculate the Total Per each day.

b. Calculate the average per day per machine based on the above totals

var dailyTotals = sampleData.GroupBy(x => x.Date)
                            .ToLookup(x => x.Key, x => x.Sum(y => y.MU));

var groupedData = (from m in sampleData
                    group m by new { m.Date, m.MachineNum } into grp
                    let dailyTotal = dailyTotals[grp.Key.Date].FirstOrDefault()
                    let dailyMachineTotal = grp.Sum(x => x.MU)
                    select new
                    {
                        Date = grp.Key.Date,
                        MachineNumber = grp.Key.MachineNum,
                        MU = (dailyMachineTotal / dailyTotal) * 100
                    }).ToList();

Upvotes: 0

Related Questions