CODEWITHSUNDEEP
c#linq
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

LINQ Lambda Group By with Sum

Hi I can do this in method syntax but I'm trying to improve my lambda skills how can I do:

SELECT SUM([job_group_quota]) as 'SUM'
FROM [dbo].[tbl_job_session]
WHERE [job_group_job_number] = @jobnum
and [job_group_ID] like @sess
GROUP BY [job_group_job_number]

I've been messing around with it but can't get it right.

lnq.tbl_job_sessions.GroupBy(a => a.job_group_job_number == jnum)
                    .Select(b => new { b.job_group_quota}).Sum();

Upvotes: 27

Views: 88148

Answers (6)

Mc Evens
Mc Evens

Reputation: 1

lnq.tbl_job_sessions.Where(x => x.job_group_job_number == @jobnum 
                          && x.job_group_ID.Contains(""+ @sess +"")
                        )
                 .GroupBy(a => a.job_group_job_number)
                 .Select(b => new 
                              { 
                                SUM = b.Sum( p => p.job_group_quota)
                              }
                        );

Upvotes: 0

Jorge Santos
Jorge Santos

Reputation: 46

Sum Ficha_Venda and Entrada from Movimento:

var query = from bd in db.Movimento
                            where (movimento.Data != null ? bd.Data == movimento.Data : bd.Data == movimento.Data)
                            && (bd.Loja == Loja)
                            group bd by bd.Data into t
                            select new {entrada = t.Sum(bd=> bd.Entrada), ficha = t.Sum(bd=> bd.Ficha_Venda)};

Upvotes: 0

detale
detale

Reputation: 12892

If you want to get a key-to-sum Dictionary result.

var allJobQuota = jobSessions.GroupBy(s => s.jobNumber)
                             .ToDictionary(g => g.Key, g => g.Sum(s => s.quota));

Upvotes: 3

Sameer Shinde
Sameer Shinde

Reputation: 51

Few Group by Examples

public void GroupBy1() { var personList = dbEntities.People.GroupBy(m => m.PersonType).Select(m => new { PersonType = m.Key, Count = m.Count() }); }

    public void GroupBy2()
    {
        var personList = dbEntities.People.GroupBy(m => new { m.PersonType, m.FirstName }).Select(m => new { PersonType = m.Key, Count = m.Count() });
    }

    public void GroupBy3()
    {
        var personList = dbEntities.People.Where(m => m.EmailPromotion != 0).GroupBy(m => new { m.PersonType, m.FirstName }).Select(m => new { PersonType = m.Key, Count = m.Count() });
    }

    public void GroupBy4()
    {
        var personList = dbEntities.People.GroupBy(m => new { m.PersonType, m.FirstName }).Where(m => m.Count() > 70).Select(m => new { PersonType = m.Key, Count = m.Count() });
    }

    public void GroupBy5()
    {
        var personList = dbEntities.People
            .GroupBy(m =>
                new
                {
                    m.PersonType
                }).Where(m => m.Count() > 70)
                    .Select(m =>
                        new
                        {
                            PersonType = m.Key,
                            Count = m.Count()
                        });

        var list1 = dbEntities.People.
            GroupBy(m => new { m.PersonType }).
            Select(m =>
                new
                {
                    Type = m.Key,
                    Count = m.Count()
                })
            .Where(
                   m => m.Count > 70
                && m.Type.PersonType.Equals("EM")
                || m.Type.PersonType.Equals("GC"));
    }

    public void GroupBy6()
    {
        var list1 = dbEntities.People.
            GroupBy(m => new { m.PersonType, m.EmailPromotion }).Select(m =>
                new
                {
                    Type = m.Key,
                    Count = m.Count()
                })
            .Where
            (
                m => m.Count > 70 && m.Type.EmailPromotion.Equals(0) &&
                (
                    m.Type.PersonType.Equals("EM") ||
                    m.Type.PersonType.Equals("GC")
                ));
    }

    public void GroupBy7()
    {
        var list1 = dbEntities.People.
            GroupBy(m => m.PersonType).
            Select(c =>
                new
                {
                    Type = c.Key,
                    Total = c.Sum(p => p.BusinessEntityID)
                });
    }

    public void GroupBy8()
    {
        var list1 = dbEntities.People.
            GroupBy(m => m.PersonType).
            Select(c =>
                new
                {
                    Type = c.Key,
                    Count = c.Count(),
                    Total = c.Sum(p => p.BusinessEntityID)
                });
    }

    public void GroupBy9()
    {
        var list1 = dbEntities.People.
            GroupBy(m => m.PersonType).
            Select(c =>
                new
                {
                    Type = c.Key,
                    Max = c.Max(),
                });
    }

Upvotes: 2

Mark
Mark

Reputation: 379

This example shows how to iterate the grouped values getting the key and totals, and how to get totals directly (like previous). Both using only lambda operator.

using System;
using System.Collections.Generic;
using System.Linq;

public class Person
{
    public string Name { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
    public int SomeValue { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        List<Person> data = GetPopulatedData();

        var totals = data.GroupBy(x =>
            new { x.Name, x.City, x.ZipCode }).Select(y =>
                y.Sum(i => i.SomeValue));

        var groupsForIterate = data.GroupBy(x =>
            new { x.Name, x.City, x.ZipCode });

        Console.WriteLine("Totals: ");
        foreach (var total in totals)
        {
            Console.WriteLine(total);
        }

        Console.WriteLine("Categories: ");
        foreach (var categ in groupsForIterate)
        {
            // You can refer to one field like this: categ.Key.Ciduad
            Console.WriteLine("Group" + categ.Key);
            Console.WriteLine(categ.Sum(x => x.SomeValue));
        }

        //Output:
        //Totals:
        //1
        //2
        //1
        //Categories:
        //Group{ Name = Mark, City = BCN, ZipCode = 00000 }
        //1
        //Group{ Name = Mark, City = BCN, ZipCode = 000000 }
        //2
        //Group{ Name = John, City = NYC, ZipCode = 000000 }
        //1
    }

    private static List<Person> GetPopulatedData()
    {
        List<Person> datos = new List<Person>()
        {
            new Person(){Name="Mark", City = "BCN",
                ZipCode = "00000", SomeValue = 1}, // group A

            new Person(){Name="Mark", City = "BCN",
                ZipCode = "000000", SomeValue = 1}, // group B

            new Person(){Name="Mark", City = "BCN",
                ZipCode = "000000", SomeValue = 1}, // group B

            new Person(){Name="John", City = "NYC",
                ZipCode = "000000", SomeValue = 1}, // group C
        };
        return datos;
    }
}

Upvotes: 0

Diego
Diego

Reputation: 20194

A general example:

query
.GroupBy(item => item.GroupKey)
.Select(group => group.Sum(item => item.Aggregate));

Upvotes: 62

Related Questions