Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ Lambda Group By with Sum

Tags:

c#

linq

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();
like image 965
bumble_bee_tuna Avatar asked Aug 07 '11 01:08

bumble_bee_tuna


3 Answers

A general example:

query
.GroupBy(item => item.GroupKey)
.Select(group => group.Sum(item => item.Aggregate));
like image 170
Diego Avatar answered Oct 02 '22 09:10

Diego


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(),
                });
    }
like image 30
Sameer Shinde Avatar answered Oct 02 '22 10:10

Sameer Shinde


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));
like image 37
detale Avatar answered Oct 02 '22 09:10

detale