Linq: Groupby, Sum and Count

Linq: GroupBy, Sum and Count

I don't understand where the first "result with sample data" is coming from, but the problem in the console app is that you're using SelectMany to look at each item in each group.

I think you just want:

List<ResultLine> result = Lines
    .GroupBy(l => l.ProductCode)
    .Select(cl => new ResultLine
            {
                ProductName = cl.First().Name,
                Quantity = cl.Count().ToString(),
                Price = cl.Sum(c => c.Price).ToString(),
            }).ToList();

The use of First() here to get the product name assumes that every product with the same product code has the same product name. As noted in comments, you could group by product name as well as product code, which will give the same results if the name is always the same for any given code, but apparently generates better SQL in EF.

I'd also suggest that you should change the Quantity and Price properties to be int and decimal types respectively - why use a string property for data which is clearly not textual?

group by and sum using linq

var list;
// manipulate list from your table;

list
  .GroupBy(t=>t.Id)
  .Select(t=>new { ID= t.Key , Value= t.Sum(u=>u.Value)}).ToList

GROUPBY and SUM on List items using LINQ

This should work for grouping by AffilateID and Date and then getting the sum (though it's weird to store a number as a string for something like this, but whatever floats your boat).

 var results = DealerTFNDatesTable
     .GroupBy(x => new { x.AffiliateID, x.Date })
     .Select(x => new DailySummary {
        AffiliateID = x.First().AffiliateID,
        Date = x.First().Date,
        TotalCalls = x.Sum(y => Convert.ToInt32(y.TotalCalls)).ToString()
     });

If you now look at the result, for example with this code, you get exactly the values you wanted:

foreach (var x in results) {
    Console.WriteLine($"id = {x.AffiliateID}, date = {x.Date}, totalCalls = {x.TotalCalls}");
}

> id = 0, date = 12/12/2016, totalCalls = 68
> id = 0, date = 12/13/2016, totalCalls = 74
> id = 1, date = 12/22/2016, totalCalls = 63

c# linq GroupBy and Sum up only the lowerest in each group

This is working for me (in LINQPad):

void Main()
{
    var data = new List<Info>
    {
        new Info(){ Book=1, Chapter=1, Paragraph=1, TimesRead=3},
        new Info(){ Book=1, Chapter=1, Paragraph=2, TimesRead=3},
        new Info(){ Book=1, Chapter=1, Paragraph=3, TimesRead=4},
        new Info(){ Book=1, Chapter=2, Paragraph=1, TimesRead=5},
        new Info(){ Book=1, Chapter=2, Paragraph=2, TimesRead=2},
        new Info(){ Book=1, Chapter=2, Paragraph=3, TimesRead=2},
        new Info(){ Book=1, Chapter=2, Paragraph=4, TimesRead=9},
        new Info(){ Book=2, Chapter=1, Paragraph=1, TimesRead=3},
    };

    var query = data.GroupBy(d => new {d.Book, d.Chapter})
    .Select(g => new
    {
        Book = g.Key.Book,
        Chapter = g.Key.Chapter,
        MinTimesRead = g.Min(d => d.TimesRead)
    });

    query.Dump();
}

public class Info
{
    public int Book { get; set; }
    public int Chapter { get; set; }
    public int Paragraph { get; set; }
    public int TimesRead { get; set; }
}

It produces the following output:

It does this by grouping on Book and Chapter, and returning an anonymous object containing the Book, Chapter, and the minimum TimesRead for that group.

The Sum of TimesRead is just a simple matter of .Sum(q => q.MinTimesRead) on that result.

LINQPad file is available here.

---

If you want access to the items within the groupings, change the Select to:

.Select(g => new
{
    Book = g.Key.Book,
    Chapter = g.Key.Chapter,
    MinTimesRead = g.Min(d => d.TimesRead),
    Items = g.Select(i => i)
});

Which will give you:

(image may be slightly out of sync with code, as I tweaked the code to be more memory efficient with larger data sets after I took the screenshot)

Linq Sum with group by

It seems to me that you just need a projection from "entry" to "effective fee" which you can sum - something like:

var result = source
    .GroupBy(x => x.Currency)
    .Select(g => new {
        Currency = g.Key,
        Total = g.Sum(x => x.FeesCustom > 0 ? x.FeesCustom : x.FeesNormal)
    });

That's equivalent to:

var result = source
    .GroupBy(x => x.Currency,
             (key, values) => new {
                Currency = key,
                Total = values.Sum(x => x.FeesCustom > 0 ? x.FeesCustom : x.FeesNormal)
             });

Or do the transformation earlier:

 var result = source
     .Select(x => new {
         x.Currency,
         x.Fee = x => x.FeesCustom > 0 ? x.FeesCustom : x.FeesNormal
     })
     .GroupBy(x => x.Currency, x => x.Fee,
              (key, values) => new { Currency = key, Fee = values.Sum() });

Linq Query using GroupBy() and Sum() and Returning Values not Grouped

Try this query:

var results = (from r in CM_Records
               join sub in CM_Records on r.Id equals sub.Id
               select new {R = r, S = sub}
               into joined
               group joined by new
               {
                  label = joined.R.ServiceDateTime.Value.Month + "/" + joined.R.ServiceDateTime.Value.Day + "/" + joined.R.ServiceDateTime.Value.Year,
                  joined.R.CategoryId
               }
               into grouped
               select new
               {
                  grouped.Key.label,
                  grouped.Key.CategoryId,
                  Value = grouped.ToList().Select(x => x.S.value).Sum()
               }).ToList();

Including the ServiceTime doesn't make much sense, unless you decide to select the max, min or something like that.

LINQ group by sum not working as expected

The reason for this is that you group by a class which doesn't override Equals and GetHashCode. Then the implementation of System.Object is used which just compares references. Since all are different references you get one group for every instance.

You could group by this property or override Equals and GetHashCode to compare this property:

list = list
    .Select(w => new tempClass { myKey = w.myKey, total = w.total })
    .GroupBy(x => x.myKey)
    .Select(y => new tempClass { myKey = y.Key, total = y.Sum(z => z.total) })
    .ToList();

Linq count and sum in join

The comments above correctly saying that group by should be used.

---

It is also good to know what keys should be used in the group by. In the select above you are listing a lot of properties and two of them should be grouped. Please take consider adding and removing some columns.
Why remove columns? Because of performance. If more columns used to group id_jadwal and total_berat_packing that has a cost at database level.

You may ask why add more columns? This can be because of correct functionality. Right now you have seven classic property, these are enough for correct summation? If not add more columns and create index for those

---

I did modify the query to fulfil grouping if you have question please let me know in the comment section.

var result = (from dbrg in db.data_barangs
           join pbrg in db.pengiriman_barangs
           on dbrg.kode_barang equals pbrg.kode_barang
           join jdkp in db.jadwal_kapals
           on pbrg.id_jadwal equals jdkp.id_jadwal
           join dplb in db.data_pelabuhans
           on jdkp.kode_pelabuhan equals dplb.kode_pelabuhan
           join drdp in db.data_redpacks
           on dbrg.kode_barang equals drdp.kode_barang
           group new { JumlahPacking = drdp.id_jadwal, TotalBerat = drdp.total_berat_packing }
           by new {
                KodeBarang = dbrg.kode_barang,
                TanggalKedatangan = jdkp.tgl_kedatangan,
                WaktuKedatangan = jdkp.waktu_kedatangan,
                NamaPelabuhan = dplb.nama_pelabuhan,
                Kota = dplb.kota,
                Provinsi = dplb.provinsi,
                NamaKapal = jdkp.kapal,
           }
           into beratAndPackingSumGroup
           select new
           {
               KodeBarang = beratAndPackingSumGroup.Key.KodeBarang,
               TanggalKedatangan = beratAndPackingSumGroup.Key.TanggalKedatangan,
               WaktuKedatangan = beratAndPackingSumGroup.Key.WaktuKedatangan,
               NamaPelabuhan = beratAndPackingSumGroup.Key.NamaPelabuhan,
               Kota = beratAndPackingSumGroup.Key.Kota,
               Provinsi = beratAndPackingSumGroup.Key.Provinsi,
               NamaKapal = beratAndPackingSumGroup.Key.NamaKapal,
               JumlahPacking = beratAndPackingSumGroup.Select(x => x.JumlahPacking).Count(),
               TotalBerat = beratAndPackingSumGroup.Sum(x => x.TotalBerat)
            });

If JumlahPacking property is not correct you can call a distinction on it: beratAndPackingSumGroup.Select(x => x.JumlahPacking).Distinct().Count() this is require more performance.

---

Related Topics